mysql left join - returning rows using NULL and a condition

Question

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....!

put the type into the join condition. on event.shift_id=shift.id and shift.type = 4 — Marc B
– Marc B, Commented May 27, 2014 at 15:59
The query should bring back subway also (in addition to mcdonalds), right? — Joseph B
– Joseph B, Commented May 27, 2014 at 16:20
Yes, Joseph, you are correct. And my brain moving slowly today didn't even realise that myself, hence my comment on @joe-taras answer below. Will you suggested solution do this? — anewvision
– anewvision, Commented May 27, 2014 at 16:30

Joseph B · Accepted Answer · 2014-05-27 16:15:51Z

1

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

answered May 27, 2014 at 16:15

Joseph B

5,7291 gold badge17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Taras · Accepted Answer · 2014-05-27 16:04:23Z

1

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'
)

edited May 27, 2014 at 16:04

answered May 27, 2014 at 15:59

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

2 Comments

anewvision Over a year ago

Thanks Joe - would this solution work even if there are no records AT ALL in the event table for a given shift, because I need to include those in the results too.. I just had a play around and I'm not sure if it works, sorry for not including that in the question.

Joe Taras Over a year ago

@anewvision: Dear, if you try this query it'll return McDonald and Subway rows (the first because exist some records but one is typed with 4, the second because don't exist linked records)

Collectives™ on Stack Overflow

mysql left join - returning rows using NULL and a condition

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related