Sql left outer join with three tables

Question

I am developing basically an e-commerce application. Application has two pages (all product and my-basket) authenticated user can add product to own basket. and I have three tables, the tables contains following data. I want to if the user adds product to own basket, these products don't exist on this user's all product page.

How should be the SQL query? I am looking query for all product page. so query's return type must be Product.

If user added any products to own basket on all product page these products shouldn't see on the all product page for this user.

PRODUCT TABLE    
+-------+--------+
|  id   | name   |
+-------+--------+
|  1    |   p1   |
|  2    |   p2   |
+-------+--------+

 USER TABLE
+-------+--------+
|  id   | name   |
+-------+--------+
|  3    |   U1   |
|  4    |   U2   |
+-------+--------+

BASKET TABLE
+-------+---------+-------------+
|  id   | fk_user | fk_product  |
+-------+---------+-------------+
|  5    |   3     |      1      |
|  6    |   4     |      2      |
+-------+---------+-------------+

So if authenticated user's id is 3. The user should see p2 product on own all product page.

Answer 1

try this:

SELECT product.name
FROM product
LEFT JOIN basket ON basket.fk_product = product.id
WHERE (basket.fk_user != 3 OR basket.fk_user IS NULL)

Check my demo query

If you want you can also join the user table but with the data you gave me is not necessary.

A left join keeps all rows in the first (product) table plus all rows in the second (basket) table, when the on clause evaluates to true.

When the on clause evaluates to false or NULL, the left join still keeps all rows in the first table with NULL values for the second table.