SQL - Find common rows-CodePudding

I have a table Wallets:

[id]   [address]

1      ABC
2      ABC
3      DEF
4      GHI
5      JKL

I have a table Cards

[company] [color] [wallet_id]

Facebook   blue     1
Facebook   blue     2
Youtube    red      3
Facebook   blue     3
Orkut      pink     4 
Microsoft  green    5
Facebook   blue     5

I want to get all the different wallet addresses that have the same cards, so if i pass wallet id 1, it should return:

 [id]   [address]
  3      DEF // Because wallet with id 1 and 3 have same blue Facebook card
  5      JKL // Because wallet with id 1 and 5 have same blue Facebook card

In this case it should not return Wallet with ID 2, even having the same card, because it is the same address (ABC) that we are doing the lookup.

I've tried a bunch of different solutions, but im confused on how to organize the SQL to do this.

I tried going with:

First select the wallet we want to lookup
- SELECT id, address FROM wallets w WHERE w.id = 1
Select all the cards of this wallet
- SELECT company, color FROM cards c WHERE c.wallet_id = w.id
Merge these two queries with INNER JOIN
- SELECT id, address FROM wallets w INNER JOIN cards c ON w.id = c.id WHERE w.id = 1 GROUP BY id

Now i need to merge the result of the query above with other wallets that have the same cards

... Here's where i cant proceed, im confused on how to do this :c

CodePudding user response：

You can join 2 copies of each table like this:

SELECT w2.*
FROM Wallets w1
INNER JOIN Cards c1 ON c1.wallet_id = w1.id
INNER JOIN Cards c2 ON c2.company = c1.company AND c2.color = c1.color
INNER JOIN Wallets w2 ON w2.id = c2.wallet_id AND w2.address <> w1.address  
WHERE w1.id = 1;

See the demo.

CodePudding user response：

Not the most intuitive of solutions but does the following work for you?

Since you only need rows from Wallets we can use a semi-join, first exclude the rows not required from Wallets based on the source Id, then correlate with Company and Color that exist when joined with Wallets for the source Id

A more elegant solution might be possible by using a lateral join, if we knew your specific RDBMS, however a semi-join will likely perform pretty well.

select * 
from wallets w
where not exists (
  select * from wallets w2 
  where w2.address = w.address and w2.id = 1
) and exists (
    select * from cards c
    where c.Wallet_id = w.id and exists (
      select * 
      from cards c2 
      join wallets w2 on w2.id = c2.Wallet_id 
      where w2.id = 1 
        and c.company = c2.company 
        and c.color = c2.color
    )
);