sql left join 2 tables

Question

I have a SQL query with a left join which works fine:

SELECT book.* FROM book
LEFT JOIN purchase ON book.book_id = purchase.book_id
WHERE purchase.user_id = 3
ORDER BY purchase.purchase_date

But I need also infos from purchase table, so I tried:

SELECT purchase.*, book.*
FROM purchase, book
LEFT JOIN purchase ON book.book_id = purchase.book_id
WHERE purchase.user_id = 3
ORDER BY purchase.purchase_date

But it does not work, I have this error message: #1066 - Not unique table/alias: 'purchase'

How can I do modify the first request to get data from purchase table also ?

In your second query you try to join the table `purchase` twice. But if you do so, both table have to be named with an alias — rabudde, Jan 05 '12 at 09:30
Why do you have a left join in your original query? The WHERE clause will turn it into an inner join. — , Jan 05 '12 at 09:56
Why, I didn't know inner join and found a working way with left join ;) — defacto, Jan 05 '12 at 10:19

score 3 · Accepted Answer · answered Jan 05 '12 at 09:27

Your 1st statement was nearly exactly what you want, you just need to name in the SELECT, which fields you want to return from purchase table.

e.g.

SELECT book.*, purchase.user_id 
FROM book 
    LEFT JOIN purchase ON book.book_id = purchase.book_id 
WHERE purchase.user_id = 3 
ORDER BY purchase.purchase_date

You don't need to list "purchase" in the FROM clause as well as in the JOIN - because you have, that is why you are seeing the error.

score 3 · Answer 2 · edited Jan 05 '12 at 09:33

You do not need to refer to the purchase table in the FROM clause - that would mean that you are both cross-joining book and purchase tables and then joining purchase table again. Because there are two instances of purchase table, they need to have unique alias - thus the error. You probably just need this:

SELECT purchase.*, book.*  
  FROM purchase LEFT JOIN purchase ON book.book_id = purchase.book_id 
 WHERE purchase.user_id = 3 ORDER BY purchase.purchase_date;

sql left join 2 tables

2 Answers2