CODEWITHSUNDEEP
mysqlsqldatabase
user9513505
user9513505

Reputation: 45

How to find the similar items from tables using SQL/mySQL query?

I have 2 tables as shown in the image.

Tables

I have created the tables using: 

CREATE TABLE Table1(id varchar(50), country varchar(50));
insert into Table1(id, country) values("AA", "Belgium");
insert into Table1(id, country) values("AA", "Hungary");
insert into Table1(id, country) values("BB", "Germany");
insert into Table1(id, country) values("BB", "Canada");
insert into Table1(id, country) values("CC", "USA");
insert into Table1(id, country) values("DD", "Norway");
insert into Table1(id, country) values("DD", "Finland");
insert into Table1(id, country) values("DD", "France");

CREATE TABLE Table2(grpid varchar(50), country varchar(50));
insert into Table2(grpid, country) values("WWW", "Belgium");
insert into Table2(grpid, country) values("WWW", "Hungary");
insert into Table2(grpid, country) values("WWW", "Japan");
insert into Table2(grpid, country) values("YYY", "USA");
insert into Table2(grpid, country) values("ZZZ", "Norway");
insert into Table2(grpid, country) values("ZZZ", "Finland");
insert into Table2(grpid, country) values("ZZZ", "France");
insert into Table2(grpid, country) values("ZZZ", "Russia");

and I need to extract data in this format:

AA - WWW

CC - YYY

DD - ZZZ

The logic is:

  1. In Table1, AA has Belgium and Hungary. In Table2, WWW also has Belgium and Hungary at least as the common item.
  2. In Table1, CC has USA. In Table2, YYY also has USA at least as the common item.
  3. In Table1, DD has Norway, Finland, France. In Table2, ZZZ also has Norway, Finland, France at least.

Can you please help me with what queries/logic I need to use to extract data in the mentioned format. It will be a great help. Thanks :)

Upvotes: 0

Views: 83

Answers (2)

forpas
forpas

Reputation: 164089

With a join of the 2 tables:

select distinct t1.id, t2.grpid
from Table1 t1 inner join Table2 t2
on t2.country = t1.country

See the demo.
Results:

| id  | grpid |
| --- | ----- |
| AA  | WWW   |
| CC  | YYY   |
| DD  | ZZZ   |

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think this is a join and some aggregation:

select t1.id, t2.grpid
from table1 t1 left join
     table2 t2
     on t1.country = t2.country
group by t1.id, t2.grpid
having count(*) = count(t2.country);   -- all in t1 match in t2

Upvotes: 1

Related Questions