CODEWITHSUNDEEP
joinsql-server-2012union
RustyShackleford
RustyShackleford

Reputation: 3667

How to add 'on' clause to union?

I am currently successfully union-ing two tables and joining on a third. 

select col1,col2
from table1

union 

select col1,col2
from table2

join(select distinct(id), date from table3) on table3.id = table2.id

how do I only union table1 and table2 where table1.col3 is in table2.id?

In other words if there is a value in table1.col3, I want to get all the data from table2 where there is a match on table2.id and union the tables.

edit:

table1 (source table)      table2                  table3
id  col1  col2             id col1 col2            id date  col4
  1. I want all records from table2
  2. I want to get records from table1 where there is match between table2.col1 and table1.id
  3. I want to get records from table1 where there is a match between table3.id and table1.id where date >='2018-12-01'

Notes: table1 and table2 are exactly the same, why i went with union.

Upvotes: 1

Views: 56

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

Your requested UNION actually makes for a fairly simple query since it can be built from 3 separate simple queries from your requirements;

SELECT col1, col2 FROM table2
 UNION ALL
SELECT col1, col2 FROM table1 WHERE id IN (SELECT col1 FROM table2)
 UNION ALL
SELECT col1, col2 FROM table1 WHERE id IN (SELECT id FROM table3 WHERE date >= '2018-12-01')

Note that UNION ALL allows for duplicates (a single row may show up once for each query) while changing to UNION will remove all duplicates.

A runnable dbfiddle example

Upvotes: 3

Related Questions