Conditional Join Different Tables

Conditional JOIN different tables

You could use an outer join:

select *
  from USER u
  left outer join EMPLOYEE e ON u.user_id = e.user_id
  left outer join STUDENT s ON u.user_id = s.user_id
 where s.user_id is not null or e.user_id is not null

alternatively (if you're not interested in the data from the EMPLOYEE or STUDENT table)

select *
  from USER u
 where exists (select 1 from EMPLOYEE e where e.user_id = u.user_id)
    or exists (select 1 from STUDENT s  where s.user_id = u.user_id)

SQL conditional join to multiple tables in one shot

Although I don't see anything wrong with COALESCE , you can try using UNION , something like this:

SELECT a.id,t.OtherFish
FROM A
LEFT JOIN(SELECT b.id,b.otherFish FROM b
          UNION ALL
          SELECT c.id,c.otherFish FROM C 
          where c.id NOT EXISTS(SELECT 1 FROM b bb WHERE bb.id = c.id)) t
ON(a.id = t.id)

Join different tables based on condition

Try this:

select 
    f.id,
        case when userkey=0 then l.name else p.name end as username
from [feature] f
    left join [liteuser] l on l.user_id = f.user_id
    left join [premium user] p on p.user_id = f.user_id

How to do conditional join on two tables in BigQuery?

Below is for BigQuery Standard SQL

#standardSQL
SELECT Scenario, Product_1, Product_2, candidates.Level, candidates.Value 
FROM (
  SELECT Scenario, Product_1, Product_2, t2.Level, 
    ARRAY_AGG(
      STRUCT(IF(t2.Level = 'NA', 'NA', IFNULL(t1.Level, t2.Level)) AS Level, IF(Value IS NULL OR t2.Level = 'NA', 0, Value) AS Value)
      ORDER BY CASE t2.Level
        WHEN 'Low' THEN CASE t1.Level WHEN 'Low' THEN 1 WHEN 'Med' THEN 2 WHEN 'High' THEN 3 END
        WHEN 'Med' THEN CASE t1.Level WHEN 'Low' THEN 77 WHEN 'Med' THEN 1 WHEN 'High' THEN 2 END
        WHEN 'High' THEN CASE t1.Level WHEN 'Low' THEN 77 WHEN 'Med' THEN 66 WHEN 'High' THEN 1 END
        ELSE 0
      END      
    )[OFFSET(0)] candidates
  FROM `project.dataset.table2` t2
  LEFT JOIN `project.dataset.table1` t1
  USING(Product_1, Product_2)
  GROUP BY Scenario, Product_1, Product_2, Level  
)  

If to apply to sample data from your question as in below example

#standardSQL
WITH `project.dataset.table1` AS (
  SELECT 'C' Product_1, 'D' Product_2, 'High' Level, 10 Value UNION ALL
  SELECT 'A', 'B', 'Med', 11 UNION ALL
  SELECT 'A', 'B', 'High', 12 UNION ALL
  SELECT 'B', 'C', 'Med', 13 UNION ALL
  SELECT 'B', 'C', 'High', 9 
),`project.dataset.table2` AS (
  SELECT 1 Scenario, 'A' Product_1, 'B' Product_2, 'Low' Level UNION ALL
  SELECT 2, 'C', 'D', 'Low' UNION ALL
  SELECT 3, 'A', 'B', 'Med' UNION ALL
  SELECT 4, 'M', 'N', 'High' UNION ALL
  SELECT 5, 'A', 'B', 'NA' 
)
SELECT Scenario, Product_1, Product_2, candidates.Level, candidates.Value 
FROM (
  SELECT Scenario, Product_1, Product_2, t2.Level, 
    ARRAY_AGG(
      STRUCT(IF(t2.Level = 'NA', 'NA', IFNULL(t1.Level, t2.Level)) AS Level, IF(Value IS NULL OR t2.Level = 'NA', 0, Value) AS Value)
      ORDER BY CASE t2.Level
        WHEN 'Low' THEN CASE t1.Level WHEN 'Low' THEN 1 WHEN 'Med' THEN 2 WHEN 'High' THEN 3 END
        WHEN 'Med' THEN CASE t1.Level WHEN 'Low' THEN 77 WHEN 'Med' THEN 1 WHEN 'High' THEN 2 END
        WHEN 'High' THEN CASE t1.Level WHEN 'Low' THEN 77 WHEN 'Med' THEN 66 WHEN 'High' THEN 1 END
        ELSE 0
      END      
    )[OFFSET(0)] candidates
  FROM `project.dataset.table2` t2
  LEFT JOIN `project.dataset.table1` t1
  USING(Product_1, Product_2)
  GROUP BY Scenario, Product_1, Product_2, Level  
)

the output is

Row Scenario    Product_1   Product_2   Level   Value    
1   1           A           B           Med     11   
2   2           C           D           High    10   
3   3           A           B           Med     11   
4   4           M           N           High    0    
5   5           A           B           NA      0      

I think, above mostly gives you what you need, but it might require some tuning that I hope you will be able to do

How to join different tables based on 'if else' conditions

You can use left join in the from and then coalesce() in the select:

Select c.*, coalesce(c.col1, s.col1) as col1, . . .
from candidate c left join
     party p  
     on p.party_id = c.party_id and c.status = 'party' left join
     symbol s
     on s.symbol_id = c.symbol_id and c.status = 'independent';

The coalesce() chooses the values for columns from the matching table (only one can match because of the condition on c.status).

Conditional join to two different tables based on 2 columns in 1 table

I hope a union will help you, like given below.

Select Col1,Col2
From  dbo.Box B
Join dbo.Source S On S.Id = b.SourceID
Where B.OverrideQueueID  is Null
Union
Select Col1,Col2
From  dbo.Box B
Join  dbo.Queue Q On Q.Id = b.SourceID
Where B.OverrideQueueID  is Not Null

Conditional join from different tables

You have to UNION Table_A & Table_B first, then join with Items as follows

Select id,index,T.description from items
join (select 1 as id, index, description from Table_A 
UNION select 2 as id, index, description from Table_B) as T
ON items.id=T.id and items.index=T.index

---

Related Topics