CODEWITHSUNDEEP
mysqlsql
Chad
Chad

Reputation: 1818

How do I select all the dealers that did not have an order?

I am trying to join two tables and only select the dealers that did not have their promo code used on any order.

How can I do this?

I'm trying this below, but it's not working right. In the example I want to get just Bob, since his promo_code hasn't been used in any orders.

SELECT d.`name`
FROM z_dealer d
LEFT OUTER JOIN z_order o ON (d.promo_code = o.promo_code)
AND o.promo_code IS NULL

Here are my tables...

mysql> select * from z_dealer;
+----+------+------------+
| id | name | promo_code |
+----+------+------------+
| 1  | John | holiday    |
| 2  | Suzy | special    |
| 3  | Bob  | laborday   |
+----+------+------------+

mysql> Select * from z_order;
+----+-------+------------+
| id | total | promo_code |
+----+-------+------------+
| 1  | 10    | holiday    |
| 2  | 20    | special    |
| 3  | 15    | holiday    |
| 4  | 45    | special    |
+----+-------+------------+

Upvotes: 1

Views: 40

Answers (3)

Samizdat
Samizdat

Reputation: 248

I'm not entirely sure why it's not working in your example code. I've created the same tables locally and when I run the script you provided I get the single 'Bob' answer.

SELECT d.name
FROM z_dealer d
LEFT OUTER JOIN z_order o ON (d.promo_code = o.promo_code)
AND o.promo_code IS NULL

What results are you seeing exactly?

Upvotes: 0

user1817927
user1817927

Reputation: 

SELECT d.`name` FROM z_dealer d LEFT JOIN z_order o ON (d.promo_code = o.promo_code) WHERE o.promo_code IS NULL

SQL JOINS

Upvotes: 1

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

Have you tried INNER JOIN? or You can try IN like this :

SELECT d.name
FROM z_dealer d
WHERE d.promo_code not in( SELECT promo_code FROM z_order)

Upvotes: 0

Related Questions