Jessevdb Jessevdb - 1 year ago 67
MySQL Question

SQL: SELECT where 2 columns from different tables are the same

I need to work with a database that contains info about (former) Presidents. I need to check if there a presidents that have the same hobbies AND are married in the same year.

So a president can have multiple hobbies in pres_hob table. And the marriage year is in the pres_mar table, in the mar_year column.

I've tried to INNER JOIN the tables in SQLite where the hobby and mar_year are equal, except for the pres_name. This way the JOIN doesnt work ofcourse, which makes sense. Im kinda new to this..

Any help is appreciated

enter image description here

Answer Source

Here's one option with multiple joins:

select p1.pres_name, p2.pres_name, ph.hobby
from pres_mar p1
     join pres_mar p2 on p1.pres_name != p2.pres_name and p1.mar_year = p2.mar_year
     join pres_hob ph on p1.pres_name = ph.pres_name
     join pres_hob ph2 on p2.pres_name = ph2.pres_name and ph.hobby = ph2.hobby

And depending on your expected results, another option using exists:

select pm.pres_name, ph.hobby
from pres_mar pm 
     join pres_hob ph on pm.pres_name = ph.pres_name
where exists (
    select 1
    from pres_mar pm2
         join pres_hob ph2 on pm2.pres_name = ph2.pres_name
    where pm.pres_name != pm2.pres_name and
          ph.hobby = ph2.hobby
    )