0

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?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
RustyShackleford
  • 3,462
  • 9
  • 40
  • 81

1 Answers1

1

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'
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • The tricky part is that table1 and table2 have over 500 columns each, they are the same except for the one column in table1, is there anyway to do the union without listing out the fields? – RustyShackleford Dec 17 '18 at 17:35
  • Unfortunately SQL Server does not support [UNION ALL CORR](https://stackoverflow.com/questions/2309943/unioning-two-tables-with-different-number-of-columns/52524364#52524364) or [SELECT * EXCEPT](https://stackoverflow.com/a/49760099/5070879). So just open SSMS, Object Explorer -> DB -> Tables -> and drag and drop column list. It will be automatically expanded without manual typing and then remove that single column. – Lukasz Szozda Dec 17 '18 at 17:39
  • Your example helps, how can I wrap your above query to create new view or table? – RustyShackleford Dec 17 '18 at 17:51
  • 1
    @RustyShackleford You need to add `CREATE VIEW view_name AS` or `INTO` clause – Lukasz Szozda Dec 17 '18 at 17:53
  • @RustyShackleford you can also dynamically query the column names in each table using the [meta data](https://www.mssqltips.com/sqlservertutorial/183/informationschemacolumns/) and [build your column list dynamically](http://www.sommarskog.se/dynamic_sql.html) if columns will change around this is worth building. – John Drinane Dec 17 '18 at 18:04
  • @johndrinane this is huge and exactly what I was looking for thank you! – RustyShackleford Dec 17 '18 at 18:05
  • I like your style @johndrinane! – RustyShackleford Dec 17 '18 at 18:09
  • @RustyShackleford LMK your crazy ideas... I need to get more code related ones out there – John Drinane Dec 17 '18 at 18:11