CODEWITHSUNDEEP
sqlsql-serversql-server-2008outer-join
esjay
esjay

Reputation: 165

Nested Outer Joins

I have a somewhat convoluted database that I am trying to get some info out of. Essentially there are three linked tables such as:

+----------+---------------+
| paint_id | painting_name |
+----------+---------------+
|        1 | pueblo sunset |
|        2 | some other    |
+----------+---------------+

Then another table

+---------+----------+
| made_id | paint_id |
+---------+----------+
|      10 |        1 |
+---------+----------+

And another table

+---------+---------+
| lexi_id | made_id |
+---------+---------+
|      20 |      10 |
+---------+---------+

And yet another table

+---------+---------+-------+
| term_id | lexi_id | term  |
+---------+---------+-------+
|      30 |      20 | Moran |
+---------+---------+-------+

I am trying to write a MS SQL query to return something like this:

+---------------+-------+
| painting_name | term  |
+---------------+-------+
| pueblo sunset | Moran |
| some other    | NULL  |
+---------------+-------+

You can see that the data is linked, but it has to go through several junctions to get there. I know I need some Left Outer Join stuff to go on, but I am having trouble nesting them.

Any help is appreciated, let me know if you need more information.

Upvotes: 4

Views: 4774

Answers (1)

Andrew
Andrew

Reputation: 8703

This is pretty basic, no need for outer joins based on your sample data. Just join the tables on the IDs: SQL Fiddle

  select
  t1.painting_name,
  t4.term

  from t1
  left outer join t2
  on t1.paint_id = t2.paint_id
  left outer join t3 
  on t2.made_id = t3.made_Id
  left outer join t4 on t3.lexi_Id = t4.lexi_id

EDIT: Too late at night apparently. Switched to outer joins.

Upvotes: 3

Related Questions