Triple join sql

Question

I Want some specific data form my sql databank while using a query. My first table contains all the teams for my app. (table 1) The second table contains the relation between an other table: This table has the fields :

• UniqueId
• TeamUniqueId (the uniqueId of table 1)

I want to have the data of the opposite of an inner join :

Select * From table_1
Full outer join table_2
on table_1.UniqueId = table_2.table_1UnqiueId
Where table_1.UniqueId IS NULL
OR table_2.UniqueId IS NULL

So this gives the right data.

---

But now i want compare this data with table_3 , And i want all the data that isnt linked with the 3th table

table_3 1. Unique ID 2. TeamUniqueId (uniqueId from first table)

To put it in a picture

Answer 1

I think I might use a more brute force approach:

select uniqueid
from ((select uniqueid, 1 as t1, 0 as t2, 0 as t3
       from table_1
      ) union all
      (select uniqueid, 0 as t1, 1 as t2, 0 as t3
       from table_2
      ) union all
      (select uniqueid, 0 as t1, 0 as t2, 1 as t3
       from table_3
      ) 
     ) t
group by uniqueid
having (sum(t1) = 1 or  -- in table1
        sum(t2) = 1     -- or table2
       ) and
       count(*) = 1;  -- in only one table

In some databases you could also use set functions. Something like this:

((select unique1
  from table_1
  union
  select unique1
  from table_2
 ) minus  -- or except
 (select unique1
  from table_1 t1
  intersect
  select unique1
  table_2 t2
 )
) minus  -- or except
select unique1
from table_3;