Select all from table even null and join

Question

I want to SELECT the all rows from meeting table even its null. My table structure:

attandance table:
id  | meeting_id  | person_id | time  
1   | 1           | 12        | 02:02
2   | 1           | 15        | 02:05
3   | 1           | 22        | 02:05
4   | 2           | 1         | 02:20
5   | 2           | 12        | 02:01
6   | 3           | 12        | 02:03

and meeting table:
id  | date       
1   | 15-12-2014           
2   | 17-12-2014   
3   | 19-12-2014
4   | 21-12-2014

The output should be:

If I SELECT the person_id 12 then it should return:

date       |time
15-12-2014 | 02:02
17-12-2014 | 02:01
19-12-2014 | 02:03
21-12-2014 | NULL

If I SELECT the person_id 1 then it should return:

date       |time
15-12-2014 | NULL
17-12-2014 | 02:20
19-12-2014 | NULL
21-12-2014 | NULL

You can learn how to perform outer joins [from this existing question](http://stackoverflow.com/q/17946221/2359271). — Air, Oct 24 '14 at 21:42

score 3 · Accepted Answer · edited May 23 '17 at 11:57

3

It is a pretty straightforward outer join between the tables:

select
    m.id,
    m.date
    a.time,
    c.someColumn
from
    meetings m
        left outer join attendance a
            on m.id=a.meeting_id
            and a.person_id=:person
        left outer join someTable c
            on m.id=c.id

I have written a more detailed answer on these sorts of joins in the question and answer: How can an SQL query return data from multiple tables

Edit: As per the comment by Andrew, the clause for the personID is in the join rather than in a normal where clause because it is an outer join. If the condition was put into the where clause as normal, it would in fact negate the outer join completely.

edited May 23 '17 at 11:57

Community

1
1

answered Oct 24 '14 at 21:37

Fluffeh

33,228
16
67
80

Spot on. You may want to explain why the constraint on person_id needs to be in the join clause. – Andrew Oct 24 '14 at 21:41
Where'd you get that indentation style? – Air Oct 24 '14 at 21:44
Thanks Fluffeh, this code is working, but in my code, i have 2 other join table thats make me cant get the result i wanted. I Left outer join 2 other table but still wont. – SuSu Oct 24 '14 at 21:48
@SuSu As long as the condition is in the join and not a where clause you should be able to get what you want from the query. I added a bit more explanation as per Andrew's comment. – Fluffeh Oct 24 '14 at 21:56
@AirThomas What's wrong with it? That's how I always write my queries so it is nice and easy to read :) – Fluffeh Oct 24 '14 at 21:56
@Fluffeh It's just different from any styles I have seen before. Personally, I would rather see all the tables at the same indentation level, and increase the indentation when continuing a clause onto a new line (your first `on` clause). But I am sure it's fine to read when you are used to it. – Air Oct 24 '14 at 22:01

Select all from table even null and join

1 Answers1