CODEWITHSUNDEEP
sqlleft-joindatabase-table
defacto
defacto

Reputation: 359

sql left join 2 tables

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 ?

Upvotes: 2

Views: 871

Answers (2)

Aurimas
Aurimas

Reputation: 2493

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;

Upvotes: 3

AdaTheDev
AdaTheDev

Reputation: 147224

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.

Upvotes: 3

Related Questions