sql - select data from reference table if it exists or empty string if it doesn't

Question

I am trying to create a query on SQL-Server that basically needs to select all the data from table1 and the related data from table2 and table3 if that data exists or empty strings if it doesn't.

For instance if the tables look like this:

table1               table2               table3
| id | name1 |       | id1 | name2 |      | id1 | name3 |
==============       ===============      ===============
|  1 | John  |       |  1  | Alice |      |  2  | Fred  |
|  2 | Bob   |       |  3  | Julie |      |  3  | Grace |
|  3 | Peter |

I want to run a query that gives me the following result:

| name1 | name2 | name 3 |
==========================
| John  | Alice |        |
| Bob   |       | Fred   |
| Peter | Julie | Grace  |

I have tried the following, only returns the last line:

select a.name1, b.name2, c.name3
from table1 a, table2 b, table3 c
where a.id = b.id1 and a.id = c.id1

How do I go about getting the result I am looking for?

Possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Tab Alleman, Mar 09 '16 at 16:24

score 2 · Accepted Answer · answered Mar 09 '16 at 15:56

Please avoid the use of implicit join syntax(comma separated) and use only the explicit join syntax's!(JOIN/LEFT JOIN/FULL JOIN) The problem with your query is that you need a left join, and not an inner join so:

SELECT a.name1,b.name2,c.name3
FROM table1 a
LEFT OUTER JOIN table2 b
 ON(a.id = b.id)
LEFT OUTER JOIN table3 c
 ON(a.id = c.id)

Just for knowledge, with implicit join syntax the syntax for left outer join in (+) on the right table condition.

sql - select data from reference table if it exists or empty string if it doesn't

1 Answers1