Extra rows being received when matching pairs in S

问题:

I am attempting to match customers who have purchased the same item, ordered by the first customer id CID. The query produces correct results but I am getting approximately 37 more rows than I should be receiving.

Upon inspection there appears to be some duplicates in this sense

Customer A | Customer B
Customer B | Customer A

This only occurs for some matches but not others

SELECT DISTINCT ca.name as CUSTOMERA, cb.name as CUSTOMERB
FROM customer ca, customer cb
INNER JOIN YRB_PURCHASE pur1 ON ca.cid = pur1.cid
INNER JOIN YRB_PURCHASE pur2 ON cb.cid = pur2.cid
WHERE pur1.title = pur2.title 
  AND ca.cid > cb.cid;

Here is a small example from the database

Jon Stewart  | Sydney Crosby
Jake Banning | James Monroe
James Monroe | Jake Banning

The last row shouldn't be returned as Jake Banning and James Monroe have already been paired up in row 2

回答1:

If you only want rows where the first name is alphabetically before the second, well just tell SQL that

SELECT DISTINCT ca.name as CUSTOMERA, cb.name as CUSTOMERB
FROM customer ca, customer cb
INNER JOIN YRB_PURCHASE pur1 ON ca.cid = pur1.cid
INNER JOIN YRB_PURCHASE pur2 ON cb.cid = pur2.cid
WHERE pur1.title = pur2.title 
  AND ca.name < cb.name;