CODEWITHSUNDEEP
mysqlsql
user_m
user_m

Reputation: 53

Show only products the user did not order

I'm getting data about products from 3 different tables and I want to show only products the user didn't order.

Table 1:

     Supplier
__________________
| id | name | .. |
|____|______|____|
| 1  | john | .. |
|____|______|____|

Table 2:

          Product
___________________________
| id | p_name| supplier_id |
|____|_______|_____________|
|  1 | phone |      1      |
|____|_______|_____________|
|  2 | watch |      1      |
|____|_______|_____________|

Table 3:

          Order
___________________________
| id |  p_id |  buyer_id   |
|____|_______|_____________|
|  1 |   1   |     10      |
|____|_______|_____________|

So in this case when the user visit the products page, I want to show the products he didn't order which is watch in this example.

My SQL query:

SELECT supplier.name, products.p_name FROM products 
INNER JOIN supplier ON supplier.id = product.supplier_id
INNER JOIN order ON product.id = order.p_id

I tried LEFT JOIN order ON product.id != order.p_id and WHERE order.p_id IS NULL, But no success.

So how to check if the user didn't order this product? Then show the rest of the products?

Upvotes: 0

Views: 505

Answers (4)

Álvaro González
Álvaro González

Reputation: 146460

It should be something like this:

SELECT supplier.name, products.p_name
FROM product
INNER JOIN supplier ON supplier.id = product.supplier_id
LEFT JOIN order ON product.id = order.p_id
WHERE order.id IS NULL

You issue a LEFT JOIN against order to also get products without a match and you discard rows without matches with order.id IS NULL. There's also no need to discard duplicate rows because products that haven't been ordered will only appear once.

+---------+
|         |
|         |
|         |
| product +--------+
|         |        |
|         | order  |
|         |        |
+---------+--------+

Upvotes: 1

user16425306
user16425306

Reputation:

This is an unusual schema; A supplier would not normally be an attribute of a 'products' table, and the details of the order would normally be held in a separate table from the orders - otherwise an order can only comprise one item, but anyway...

DROP TABLE IF EXISTS suppliers;
CREATE TABLE suppliers
(id INT AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(12) UNIQUE
);

INSERT INTO suppliers VALUES 
(1,'john');

DROP TABLE IF EXISTS products;

CREATE TABLE products
(id INT AUTO_INCREMENT PRIMARY KEY
,product_name VARCHAR(12) UNIQUE
,supplier_id INT NOT NULL
);

INSERT INTO products VALUES 
(1,'phone',1),
(2,'watch',1);

DROP TABLE IF EXISTS orders;

CREATE TABLE orders
(id INT AUTO_INCREMENT PRIMARY KEY
,product_id INT NOT NULL
,buyer_id INT NOT NULL
);

INSERT INTO orders VALUES
(1,1,10);

...

SELECT p.* 
  FROM products p 
  LEFT 
  JOIN orders o 
    ON o.product_id = p.id 
 WHERE o.id IS NULL;
+----+--------------+-------------+
| id | product_name | supplier_id |
+----+--------------+-------------+
|  2 | watch        |           1 |
+----+--------------+-------------+

Upvotes: 0

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

You can use WHERE NOT EXISTS statement for you case:

SELECT suppliers.name, products.name 
FROM products 
INNER JOIN suppliers ON suppliers.id = products.supplier_id
WHERE NOT EXISTS (
    SELECT product_id FROM orders WHERE orders.product_id = products.id
);

SQL fiddle here

Upvotes: 1

Jelle
Jelle

Reputation: 798

You can use a WHERE product NOT IN to exclude specific products, shown below.

SELECT supplier.name, products.p_name
FROM products
INNER JOIN supplier ON supplier.id = products.supplier_id
WHERE products.id NOT IN (
    SELECT p_id FROM order WHERE buyer_id = supplier.id
)

Within the WHERE statement, you select the product id's of all the orders from a specific user. By applying NOT IN all of these products will be excluded in your list.

Upvotes: 2

Related Questions