CODEWITHSUNDEEP
sql
Jamesla
Jamesla

Reputation: 1418

SQL join - displaying data where partial result contains a null column

I am writing the following stored procedure in mssql.

The columns foo and bar are in table1 and id is in table2.

select foo, bar, id from table1
join table2 on table2.foo = foo.foo

My issue is that if there is only data for foo and bar I want it to return

a, b, null

However it will not return a row at all if id doesn't exist but foo and bar do.

Is there an elegant way to make this work?

Upvotes: 2

Views: 87

Answers (2)

Fabio
Fabio

Reputation: 23510

I think you are looking for a FULL OUTER JOIN query

SELECT a.foo, a.bar, a.id 
FROM table1 a
FULL OUTER JOIN table2 b 
ON a.foo = b.foo

You can have a check to this visual explanation of joins

Upvotes: 0

jdotjdot
jdotjdot

Reputation: 17092

This should solve your problem:

SELECT foo, bar, id FROM table1
OUTER JOIN table2 on table2.foo = foo.foo

Upvotes: 1

Related Questions