CODEWITHSUNDEEP
sqlsql-serverjoin
dev
dev

Reputation: 181

SQL - full outer join

i have three table in sql database

enter image description here

i need to join this three table to get table like this

enter image description here

I want to join three table to get all data from whole first, third and second (where sort is 2) table

I try this query 

  select table1.Item, table1.Location, table1.Type,
         table2.Item, table2.Location, table2.Type,table2.Sort
         table3.Item, table3.Location, table3.Type
   from table1
 full outer join table2
  on table1.Item = table2.Item
  and table1.Location = table2.Location
  and table1.Type = table2.Type
  and table2.Sort = '2'
 full outer join table3
  on table1.Item = table3.Item
  and table1.Location = table3.Location
  and table1.Type = table3.Type

but i only get this table

enter image description here

How to make query to get table i want with all combination?

Any idea?

Upvotes: 3

Views: 1345

Answers (4)

athar13
athar13

Reputation: 382

You get the required result by the below query

SELECT d.item, d.location, d.[type],
    s.item, s.location, s.[type], s.sort,
    h.item, h.location, h.[type]
FROM tblData d
    FULL OUTER JOIN tblSearchData s
        ON d.item = s.item 
        AND d.location = s.location
        AND d.[type] = s.[type]
    FULL OUTER JOIN tblHelpData h
        ON (s.item = h.item OR d.item=h.item)
        AND (s.location = h.location OR d.location=h.location)
        AND (s.[type] = h.[type] OR d.[type]=h.[type])
WHERE s.sort=2 OR s.sort IS NULL

Upvotes: 1

Stefano Zanini
Stefano Zanini

Reputation: 5926

Your approach is correct, all you're missing is using coalesce in your second join criterias.

The first outer join will return you a set of rows with some null values in it, and if you use those values in the third join criterias, the condition won't be satisfied.

You can solve this problem using coalesce, that will use the second parameter if the first one is null

select  table1.Item, table1.Location, table1.Type,
        table2.Item, table2.Location, table2.Type,table2.Sort
        table3.Item, table3.Location, table3.Type
from    table1
full outer join table2
on      table1.Item = table2.Item and
        table1.Location = table2.Location and
        table1.Type = table2.Type
full outer join table3
on      coalesce(table1.Item, table2.Item) = table3.Item and
        coalesce(table1.Location, table2.Location) = table3.Location and
        coalesce(table1.Type, table2.Type) = table3.Type
where   coalesce(table2.Sort, 2) = 2

Upvotes: 5

SqlZim
SqlZim

Reputation: 38073

select table1.Item, table1.Location, table1.Type,
       table2.Item, table2.Location, table2.Type, table2.Sort,
       table3.Item, table3.Location, table3.Type
from table1
  full outer join table3
    on  table1.Item = table3.Item
   and table1.Location = table3.Location
   and table1.Type = table3.Type
  full outer join table2
    on (table1.Item = table2.Item
   and table1.Location = table2.Location
   and table1.Type = table2.Type
   and table2.Sort = '2'
   )
   or (table3.Item = table2.Item
   and table3.Location = table2.Location
   and table3.Type = table2.Type
   and table2.Sort = '2'
   )
where (table2.Sort = '2' or table2.Sort is null)

rextester demo: http://rextester.com/MNW25175

results

+------+----------+-------+------+----------+-------+------+------+----------+-------+
| Item | Location | Type  | Item | Location | Type  | Sort | Item | Location | Type  |
+------+----------+-------+------+----------+-------+------+------+----------+-------+
| 123  | A        | small | NULL | NULL     | NULL  | NULL | 123  | A        | small |
| NULL | NULL     | NULL  | 123  | A        | big   | 2    | 123  | A        | big   |
| NULL | NULL     | NULL  | 123  | B        | small | 2    | NULL | NULL     | NULL  |
+------+----------+-------+------+----------+-------+------+------+----------+-------+

This also works, but only because table2 has a match for all rows in table1, which might not fit your actual use case:

select table1.Item, table1.Location, table1.Type,
       table2.Item, table2.Location, table2.Type, table2.Sort,
       table3.Item, table3.Location, table3.Type
from table1
  full outer join table3
    on  table1.Item = table3.Item
   and table1.Location = table3.Location
   and table1.Type = table3.Type
  full outer join table2
    on table3.Item = table2.Item
   and table3.Location = table2.Location
   and table3.Type = table2.Type
   and table2.Sort = '2'
where (table2.Sort = '2' or table2.Sort is null)

Upvotes: 3

Mansoor
Mansoor

Reputation: 4192

Use LEFT OUTER JOIN :

 SELECT *  FROM Table 2 
 LEFT OUTER JOIN Table 1 ON table1.Item = table2.Item AND  table1.Location =  
                  table2.Location AND table1.Type = table2.Type and 
                  table2.Sort = '2'
 LEFT OUTER JOIN Table 3 ON table1.Item = table3.Item
                 and table1.Location = table3.Location
                 and table1.Type = table3.Type

Upvotes: 1

Related Questions