I think my Join is wrong - 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 2008 Forums

Transact-SQL (2008)

I think my Join is wrong

Author

Topic

SQL_Beginner_83
Starting Member

2 Posts

Posted - 2013-08-02 : 04:16:27

Hi All,

I'm trying to join a table to itself to find some duplicate data. So I've created a temp table with the fields I need. So I want to get all the duplicates of items sold in two shops. For example, Shop A, receipt ID 1 has 4 Items, milk, eggs, sugar and flour bought on 24 Dec 2012. I want to get its match in Shop B, a receipt that has milk, eggs, sugar and flour on 24 Dec 2012. So I'm joining my temp table to itself and joining on date and Items but I'm getting results that don't have all the items in both receipts. For example, in shop A receipt 1 has milk, eggs, flour. I'm getting a match on it from shop B receipt 4 that has milk and eggs. How can I make sure only the records that are returned has all the same items.


SELECT  *
FROM    #DUPLICATES A
INNER JOIN  #DUPLICATES B
ON      A.DATE_SOLD = B.DATE_SOLD
AND     A.ITEMS = B.ITEMS

Thanks in advance :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-02 : 04:25:12

so you just have to consider only items? or does the date also have to be same?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQL_Beginner_83
Starting Member

2 Posts

Posted - 2013-08-02 : 04:29:16

The date needs to be the same also.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-02 : 05:39:28

[code]
;With CTE
AS
(
SELECT DATE_SOLD,RECEIPTNo,
STUFF((SELECT ',' + ITEMS
FROM #DUPLICATES
WHERE RECEIPTNo = d.RECEIPTNo
AND DATE_SOLD = d.DATE_SOLD
ORDER BY ITEMS
FOR XML PATH('')),1,1,'') AS ITEMLIST
FROM (SELECT DISTINCT DATE_SOLD,RECEIPTNo FROM #DUPLICATES) d
)

SELECT c1.RECEIPTNo,c2.RECEIPTNo
FROM CTE c1
INNER JOIN CTE c2
ON c1.DATE_SOLD = c2.DATE_SOLD
AND c1.ITEMLIST = c2.ITEMLIST
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources