Jessevdb Jessevdb - 8 months ago 45
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


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