Patrick Gregorio Patrick Gregorio - 2 months ago 6
SQL Question

SQL JOIN Multiplies Result Set - Cartesian Product

I have a base table and I want to join 2 more tables to get all the data I need. Upon running my query I get a Cartesian product because 2 of the tables (the main table and the other table I am joining) is being joined by a

non-unique
key.

Here's an example:

Invoice (i)



id order_id name comment
-----------------------------------------------------------------
1 500 Males Mice
2 500 Females Mice
3 500 Courier Fee Within City


Order (o)



id order_number
------------------------------
500 AN5246516264


Order Items (oi)



id order_id strain_id species_id comments
-----------------------------------------------------------------
1100 500 858 9876 Mice
1101 500 858 9876 Mice
1102 500 NULL NULL Within City


Using the tables defined above here's my query:

Query



SELECT
i.name, i.comment,
o.order_number,
oi.strain_id, oi.species_id
FROM invoice i
LEFT JOIN order o
ON i.order_id = o.id
LEFT JOIN order_items oi
ON o.id = oi.order_id


After running the query I get a Cartesian product as shown below (not necessarily in order):

name comment order_number strain_id species_id
-----------------------------------------------------------------------
Males Mice AN5246516264 858 9876 ---> I want this row
Males Mice AN5246516264 858 9876
Males Within City AN5246516264 NULL NULL
Females Mice AN5246516264 858 9876
Females Mice AN5246516264 858 9876 ---> I want this row
Females Within City AN5246516264 NULL NULL
Courier Fee Mice AN5246516264 858 9876
Courier Fee Mice AN5246516264 858 9876
Courier Fee Within City AN5246516264 NULL NULL ---> I want this row


I understand what's happening here and I know why it results in a Cartesian product but I don't know how to solve my problem.

I just want to append the
order_number
,
strain_id
and the
species_id
to the
Invoice (i)
table.

And yes the table structure is built somewhat in a weird way. I do believe the database designer was intoxicated when he made the tables but that's not something I can change now.

Any suggestions?

EDIT



I've looked at all 3 tables and I can't find any other columns to join them with. I'm left with the
order_id
.

Answer

You could number rows in tables i and oi at first using function row_number() and then use it as part of joining condition:

with i as  (select row_number() over (partition by order_id order by id) rn, i.*  
              from invoice i),
     oi as (select row_number() over (partition by order_id order by id) rn, oi.* 
              from order_items oi)
select i.name, i.comments, o.order_number, oi.strain_id, oi.species_id
  from i left join orders o on i.order_id = o.id
  left join oi on oi.order_id = o.id and oi.rn = i.rn

Test data and output:

create table invoice (id number(4), order_id number(4), 
  name varchar2(15), comments varchar2(20));
insert into invoice values (1, 500, 'Males', 'Mice');
insert into invoice values (2, 500, 'Females' ,'Mice');
insert into invoice values (3, 500, 'Courier Fee', 'Within City');
--
create table orders (id number(4), order_number varchar2(15));
insert into orders values (500, 'AN5246516264');
--
create table order_items(id number(5), order_id number(4), 
  strain_id number(5), species_id number(5), comments varchar2(20));
insert into order_items values(1100, 500, 858,  9876, 'Mice');
insert into order_items values(1101, 500, 858,  9876, 'Mice');
insert into order_items values(1102, 500, NULL, NULL, 'Within City');

Output:

NAME            COMMENTS             ORDER_NUMBER    STRAIN_ID SPECIES_ID
--------------- -------------------- --------------- --------- ----------
Males           Mice                 AN5246516264          858       9876
Females         Mice                 AN5246516264          858       9876
Courier Fee     Within City          AN5246516264              
Comments