CODEWITHSUNDEEP
mysqlsqljoinnull
anewvision
anewvision

Reputation: 105

mysql left join - returning rows using NULL and a condition

I have two tables: 'shift' and 'event'. One shift has many events.

Some sample data:

---- shift ---- 
id  - location
 1  - mcdonalds
 2  - burger king
 3  - subway

 --- event ---
 id - shift_id - type - note
 1  - 1        - 2    - bingo
 2  - 1        - 3    - bingo
 3  - 2        - 4    - ignore
 4  - 2        - 2    - ignore

An event has a type, so for example: type '4' represents an event starting a shift.

I want to build a query that returns all shifts that DO NOT have an event record where type = 4. So in the example above, I would bring back mcdonalds.

I've got as far as:

     SELECT 
     shift.location AS location,
     FROM shift
     LEFT JOIN event ON event.shift_id=shift.id

and that's where I get stuck. Obviously one can add

 WHERE event.type IS NULL

..but how does one only return rows where the null is for type = 4 ?

Thanks....!

Upvotes: 1

Views: 95

Answers (2)

Joseph B
Joseph B

Reputation: 5669

The following query selects all the shifts that have type=4 in the event table. Then a LEFT JOIN is made between this data and the shift table. Rows having event.shift_id are selected (meaning that shift_id's that do not have type = 4).

 SELECT 
 shift.*
 FROM shift
 LEFT JOIN (SELECT DISTINCT shift_id FROM event WHERE type = 4) event ON event.shift_id=shift.id
 WHERE event.shift_id IS NULL;

SQL Fiddle demo

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15399

If you use LEFT JOIN you can discard the rows with type = 4 but if you have a shift with two events, one equals to 4 and another not equals... You'll get that shift because exists a row different by 4. When you use NOT EXISTS you do an analysys about all rows of your subquery.

Try this:

SELECT *
FROM shift s
WHERE NOT EXISTS(
   SELECT 'EVENT'
   FROM event e
   WHERE e.shift_id = s.id
   AND e.type = '4'
)

Upvotes: 1

Related Questions