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
Here's one option with multiple
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
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 )