CODEWITHSUNDEEP
sqljoin
Callum Gilbanks
Callum Gilbanks

Reputation: 33

Condition SQL Join

We have a table of users where most have a school number attached. I've then used an Inner Join to join the school's table to get the name of the school. Some users don't have a school number so there is a NULL value - which means that none of their data is appearing. Is there a way I can do a conditional join dependent on the schoolid field?

Users Table:

Name | Schoolid
-----|---------
John | 27
Fred | 49
Sam  | NULL

School Table:

Schoolid  | Schoolname
----------|-----------
27        | John's School
49        | Fred's School

When the tables are Joined on the Schoolid the results are

Name | Schoolname
-----|-----------
John | John's School
Fred | Fred's School

Ideally I would like the results to look like this:

Name | Schoolname
-----|-----------
John | John's School
Fred | Fred's School
Sam  | NULL

Can anybody help? Is it something simple and I'm just being an idiot?

Thanks

Upvotes: 1

Views: 51

Answers (2)

Kaganar
Kaganar

Reputation: 6570

You're looking for an outer join.

E.g.

select * from Users left outer join School on Users.Schoolid = School.Schoolid

Microsoft has an article with some examples that may make it more clear, even if you're using a different SQL dialect.

Upvotes: 3

Matt
Matt

Reputation: 15071

SELECT * 
FROM Users u
LEFT OUTER JOIN School s ON u.Schoolid = s.Schoolid

Or even.

SELECT * 
FROM Users u
FULL OUTER JOIN School s ON u.Schoolid = s.Schoolid

Upvotes: 0

Related Questions