Matching Record 2 - 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 Record 2

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-03-02 : 22:18:38

hi

I have previously posted a similar thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119353

and i thought i was working well until the PK values is different.

Ok, this is what i have: Notice that the ID of both tables are different.

tblBuy
ID, ProductType, Locations, BuyerName, UserID
1 A West John 1
2 B West Kent 1
3 B West Sally 1

tblSell
ID, ProductType, Locations, SellerName, UserID
1001 A West Clark 1
1002 B West Jim 1
1003 B West Kim 1

and using the below SQL:

SELECT 'Buyer' AS [Buyer/Seller],
b.BuyerName,b.ProductType,b.Locations,COALESCE(s.ID,0) AS Match
FROM tblBuy b
OUTER APPLY (SELECT TOP 1 ID
FROM tblSell
WHERE ProductType=b.ProductType
AND Locations=b.Locations
ORDER BY ID) s
WHERE b.UserID=1
UNION ALL
SELECT 'Seller' AS [Buyer/Seller],
s.SellerName,s.ProductType,s.Locations,COALESCE(b.ID,0) AS Match
FROM tblSell s
OUTER APPLY (SELECT TOP 1 ID
FROM tblBuy
WHERE ProductType=s.ProductType
AND Locations=s.Locations
ORDER BY ID) b
WHERE s.UserID=1

The criteria for matching is by both ProductType and Location so when John the buyer from tblBuy wants to buy product "A" from and from location "West", it would try to match tblSell both ProductType and Location and the reults is 1 and for buyer Kent the result match would be 2. And seller Kim from tblSell would also try to match both ProductType and Location with tblBuy, and the result is 2. Now, all these results are based on logon UserID which is 1. Also, would like to display a 0 when there isn't a match meaning someone wants to buy but nobody sells or vice-versa, so it would display a 0.
How should i go about it? Thanks

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-03-03 : 07:45:05

hi

Ok, I got it working. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources