Matching rows from 2 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Matching rows from 2 tables

Author

Topic

adrian01
Starting Member

5 Posts

Posted - 2010-04-08 : 01:27:10

Hi,

I have 2 tables (request and result).
Table request have the following columns:
- ID (running int)
- mobile_phone (varchar)
- date_inserted (datetime)
- parameters (varchar)
- consumed (0 or 1)
- matched (0 or 1)

Table result have the following columns:
- ID (running int)
- mobile_phone (varchar)
- date_inserted (datetime)
- parameters (varchar)
- results (varchar)

And I have a multi-thread application running that is taking each entry from the request table where consumed = 0, send it to a remote process via HTTP, then update the consumed = 1.

When we receive the result from the remote process (via HTTP as well), we then try to find a matching request by selecting the most recent request (within 5 minutes) with matching mobile_phone number and matched = 0. We have to use this matching method because the remote process doesn't give us any form of transaction_id. (We have no control over this remote process).
After finding the matching request, we insert it into the result table (which is then being used for other purposes).

Now the problem is, sometimes my customer input several requests from the same mobile_phone with DIFFERENT parameters into the request table. My application would send all those requests to the remote process, and there are cases when I received the results (all of them) at the same time. So instead of each result taking different request, what happened is that they all are being matched to 1 request.

To illustrate the scenario, let's say we have 3 entries, and after the process, this is what we have:
For table request:
(ID,mobile_phone,date_inserted,parameters,consumed,matched)
1,'16177709089','2010-04-08 04:00:00','Apple',1,0
2,'16177709089','2010-04-08 04:00:00','Orange',1,1
3,'16177709089','2010-04-08 04:00:00','Banana',1,0

For table result:
(ID,mobile_phone,date_inserted,parameters,result)
1,'16177709089','2010-04-08 04:01:00','Orange','some result'
2,'16177709089','2010-04-08 04:01:00','Orange','some result'
3,'16177709089','2010-04-08 04:01:00','Orange','some result'

What we should have is:
For table request:
(ID,mobile_phone,date_inserted,parameters,consumed,matched)
1,'16177709089','2010-04-08 04:00:00','Apple',1,1
2,'16177709089','2010-04-08 04:00:00','Orange',1,1
3,'16177709089','2010-04-08 04:00:00','Banana',1,1

For table result:
(ID,mobile_phone,date_inserted,parameters,result)
1,'16177709089','2010-04-08 04:01:00','Apple','some result'
2,'16177709089','2010-04-08 04:01:00','Orange','some result'
3,'16177709089','2010-04-08 04:01:00','Banana','some result'

This is part of our SQL Statement that does the matching and update:

SELECT TOP 1 @ID = ID, @parameter = parameter
FROM request WHERE date_inserted >= @5_mins_ago and mobile_phone = @mobile_phone;

UPDATE request SET matched = 1 WHERE ID = @ID;

How come when 3 results coming together, all 3 of them are able to pick only one request (in our sample case, is parameters = Orange). Shouldn't it be some kind of locking?

Any advise is appreciated.

Thanks,

Adrian

sathiesh2005
Yak Posting Veteran

85 Posts

Posted - 2010-04-08 : 03:28:25

Hi,

In my understanding, your are going to update only the consumed=0 records, so you can take consumed column also in the where clause.
if it is already 1 you can ignore the second and third one in your sample.
Please ignore this if my understanding is wrong.

Regards,
Sathieshkumar. R

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources