Matching Records - 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 Records

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-02-06 : 04:37:34

hi

I have 2 tables namely: tblBuy(Field: ID,BuyType,userid) and tblSell (Field: ID,SellType,userid)

Basically. Users that post to buy would match those that post to sell later and vice-versa

For example, User A entered into tblBuy to buy Type "A" and later User B entered into tblSell Type "A" and later User C entered into tblSell Type "A"

As user A logon to the system, the first thing he would see is this:

Buy Match
A 2

or if nothing then

Buy Match
A 0

The same is also true when User D entered into tblSell Type "B" and later Users E and F entered into tblBuy Type "B"

As user D logon to the system, the first thing he would see is this:

Sell Match
B 2

or if nothing then

Sell Match
B 0

How should it go about it? Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-06 : 04:58:05

check this is ur required query
declare @tblBuy table( ID int,BuyType varchar(6),userid int)
insert into @tblbuy select 1,'a',1 union all
select 2,'b',2

declare @tblSell table ( ID int,SellType varchar(32),userid int)
insert into @tblsell
select 1,'b',1 union all
select 2,'c', 1 union all
select 3,'e',2 union all
select 4,'f',2 union all
select 5,'g',2

select distinct b.buytype, cnt from @tblbuy b inner join (select count(userid)as cnt,userid from @tblsell group by userid) s on s.userid = b.userid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 05:04:41

[code]SELECT b.BuyType AS theType,
b.UserID AS Buyer,
s.UserID AS Seller,
~~COUNT(*)~~
FROM tblBuy AS b
FULL JOIN tblSell AS s ON s.SellType = b.BuyType
WHERE @UserID IN (b.UserID, s.UserID)
GROUP BY b.BuyType,
b.UserID,
s.UserID[/code]

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-06 : 05:04:49

quote:

Originally posted by Peso

SELECT		b.UserID,
		b.BuyType,
		s.UserID,
		s.SellType
FROM		tblBuy AS b
FULL JOIN	tblSell AS s ON s.SellType = b.BuyType
WHERE		@UserID IN (b.UserID, s.UserID)

E 12°55'05.63"
N 56°04'39.26"

hai peso
output as
type, match (count of match records)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 05:07:15

Yes, that's why I removed it and posted a better solution.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 05:18:40

[code]SELECT ProductType,
MAX(PartnerType) AS PartnerType,
COUNT(DISTINCT Partners) AS Partners
FROM (
SELECT b.BuyType AS ProductType,
CASE b.UserID
WHEN @UserID THEN 'I buy'
ELSE 'I sell'
END AS PartnerType,
CASE b.UserID
WHEN @UserID THEN s.UserID
ELSE b.UserID
END AS Partners
FROM tblBuy AS b
FULL JOIN tblSell AS s ON s.SellType = b.BuyType
WHERE @UserID IN (b.UserID, s.UserID)
) AS d
GROUP BY ProductType
ORDER BY ProductType[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 05:22:28

[code]SELECT 'I sell' AS Header,
b.BuyType AS ProductType,
COUNT(DISTINCT b.UserID) AS Partners
FROM tblBuy AS b
INNER JOIN tblSell AS s ON s.SellType = b.BuyType
WHERE s.UserID = @UserID
GROUP BY b.BuyType

UNION ALL

SELECT 'I buy' AS Header,
s.SellType AS ProductType,
COUNT(DISTINCT s.UserID) AS Partners
FROM tblSell AS s
INNER JOIN tblBuy AS b ON b.BuyType = s.SellType
WHERE b.UserID = @UserID
GROUP BY s.SellType[/code]

E 12°55'05.63"
N 56°04'39.26"

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-02-06 : 11:40:05

Thank you so much. I will do some runs on the actual data.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-02-07 : 10:54:03

hi

Ok, it seems that there is a little changes here to the table schemas.

Now, i have 2 tables namely tblSell and tblBuy and the fields for both table are

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

tblSell
ID, ProductType, Locations, SellerName, UserID
1 A West Clark 2
2 B West Jim 2
3 B West Kim 1

Now if i logon as UserID 1 then the result should match by both ProductType and location. And the final result would look like this:

Buyer/Seller ProductType Location Match
Buyer John A West 1
Buyer Kent B West 2
Seller Kim B West 2

How should i go about it. I did try to tweak the given solution but to no avail. I am sorry for the inconvenience caused.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 11:52:14

where does Match value come from?

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-02-07 : 12:09:44

hi

It come from both tables. For example, because 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. Thanks

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-02-07 : 12:14:14

In fact, if there is a need to change the DB design to get the results. Is fine with me, as long as it produce the result set. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 14:02:58

[code]
SELECT 'Buyer' AS [Buyer/Seller],
b.BuyerName,b.ProductType,b.Locations,s.ID AS Match
FROM tblBuy b
CROSS 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,b.ID AS Match
FROM tblSell s
CROSS APPLY (SELECT TOP 1 ID
FROM tblBuy
WHERE ProductType=s.ProductType
AND Locations=s.Locations
ORDER BY ID) b
WHERE s.UserID=1
[/code]

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-02-07 : 20:51:06

Thanks so much. It works wonderful.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-08 : 01:18:48

welcome

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-02-08 : 12:16:31

Very sorry. One last question. I wish to display a 0 match.

For example, based on the data given, all seems to have a match. I 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. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-08 : 21:16:29

modify like below

         
 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

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-02-08 : 21:32:21

Thanks, It work fine now. Thanks so much again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-08 : 21:35:51

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources