SQL cartesian problem

Nulty · 04-05-2012 03:14PM #1

I've got a left outer join query returning a cartesian product and I can't see where the problem is. Can you have a look please?

SELECT username, loan_date, copy_id, book_title 
FROM members m 
LEFT OUTER JOIN membership_card mc ON m.mem_id = mc.mem_id 
LEFT OUTER JOIN loans l ON l.mem_id = mc.mem_id 
LEFT OUTER JOIN book_copies bc ON l.isbn = bc.isbn 
LEFT OUTER JOIN books b ON b.isbn = bc.isbn;

Will provide ERD screenshot in a sec

Nulty · 04-05-2012 03:26PM

It just clicked :rolleyes:

SELECT username, loan_date, copy_id, book_title 
FROM members m 
LEFT OUTER JOIN membership_card mc ON m.mem_id = mc.mem_id 
LEFT OUTER JOIN loans l ON l.mem_id = mc.mem_id 
LEFT OUTER JOIN book_copies bc ON l.isbn = bc.isbn AND bc.copy_id = l.copy_id 
LEFT OUTER JOIN books b ON b.isbn = bc.isbn;

The loans table has two foreign keys in book copies fields, I needed to specify the join on both keys.

SQL cartesian problem

Comments