How to join two tables, by looking up values from three tables?

0
votes

I want to create a new table from 3 tables:

  1. table1 is source table
  2. table2 is subset of tabl1 except with one extra column
  3. table3 has different columns.

All these tables are linked by ID column.

I know I will need a vertical join (UNION).

Query:

  1. I need to join all records in table2 and look up values from table2.field1 and get the record set from table1 where field1 in table2 is in table1.id
  2. I need to subset table3 where table3.date >= '2010-10-01' and use table3.id to get the record set from table1 where table3.id = table1.id

How do I use the UNION join to look up values, from table1 and keep all records from table2?

1
sqlsql-servertsqlsql-server-2012union
It will be good to post schema sample data and expected output in text format. – Suraj Kumar

1 Answers

1
votes

You could use:

--CREATE VIEW my_view AS 
SELECT sub.id, sub.col1, sub.col2, table3.col1, ...
-- INTO #temp_table -- alternatively
FROM (SELECT id,col1, col2, ... -- only common column list, drag and drop from
      FROM table1               -- object explorer
      UNION 
      SELECT id, col1, col2, ...
      FROM table2) sub
JOIN table3 ON table3.id = sub.id 
WHERE table3.date >= '2010-10-01'