sql for a table with two foreign keys to same table causing Cartesian product

I am fairly new to SQL and I am stuck with this query. So I have a table that contains two foreign keys to the same table. The result I require just won’t show up using joins. I have created a sqlfiddle here

Although the sqlfiddle is on MySQL, I have the same problem on Oracle.

The two tables look like this:

Table wedding

wedding_id|civil_union_side_a|civil_union_side_b 
________________________________________________
1         |Smith             |Jones

Table guest

guest_side|guest_name
_____________________
Smith     |John
Smith     |Jill
Smith     |Mark
Jones     |Jane
Jones     |David
Jones     |Mel

Now the result that I require should look like this:

wedding_id|civil_union_side_a|civil_union_side_b|guest_side|guest_name
_______________________________________________________________________
1         |Smith             |Jones             |Smith     |John
1         |Smith             |Jones             |Smith     |Jill
1         |Smith             |Jones             |Smith     |Mark
1         |Smith             |Jones             |Jones     |Jane
1         |Smith             |Jones             |Jones     |David
1         |Smith             |Jones             |Jones     |Mel

which i basically achieved using a union:

select * 
from wedding 
     inner join guest guest_side_a
on wedding.civil_union_side_a = guest_side_a.guest_side 

union all

select * 
    from wedding 
inner join guest guest_side_b
on wedding.civil_union_side_b = guest_side_b.guest_side 

However, when I try to join the tables, I get a Cartesian Product. Here is the query I have:

select * 
from wedding wedding
      inner join guest guest_side_a
on wedding.civil_union_side_a = guest_side_a.guest_side
      inner join guest guest_side_b
on wedding.civil_union_side_b = guest_side_b.guest_side;

and the incorrect result I get (9 Rows instead of 6):

wedding_id|civil_union_side_a|civil_union_side_b|guest_side|guest_name|guest_side1|guest_name1
_______________________________________________________________________________________________
1        |Smith             |Jones             |Smith     |John      |Jones       |Jane
1        |Smith             |Jones             |Smith     |Jill      |Jones       |Jane
1        |Smith             |Jones             |Smith     |Mark      |Jones       |Jane
1        |Smith             |Jones             |Smith     |John     |Jones        |David
1        |Smith             |Jones             |Smith     |Jill     |Jones        |David
1        |Smith             |Jones             |Smith     |Mark      |Jones       |David
1        |Smith             |Jones             |Smith      |John     |Jones       |Mel
1        |Smith             |Jones             |Smith      |Jill     |Jones       |Mel
1        |Smith             |Jones             |Smith      |Mark     |Jones       |Mel

Is there a way to get the desired result with a join and without a union ?


Source: oracle

Leave a Reply