SQLTeam.com | Weblogs | Forums

3 way UNION ? how to bring type of table into result set,

I have 3 way union, including decimal values that occur in 3 different tables coming through on separate rows in dataset. So if its from tableA , the values in tableB & table C are not returned ,just the same number of columns in each union.
What I need is a column returned with the name of table A, B or C corresponding to the union effect.
I searched everywhere, but to no avail.

If you are excluding rows, then you'll probably want to use EXCEPT rather than UNION:

Sounds like you want rows that are unique to each table, then UNION all those unique rows together. If that's the case, this should work:

;WITH tableA_cte AS(SELECT col1, col2 FROM tableA 
EXCEPT SELECT col1, col2 FROM tableB
EXCEPT SELECT col1, col2 FROM tableC),
,tableB_cte AS (SELECT col1, col2 FROM tableB 
EXCEPT SELECT col1, col2 FROM tableA
EXCEPT SELECT col1, col2 FROM tableC)
,tableC_cte AS (SELECT col1, col2 FROM tableC
EXCEPT SELECT col1, col2 FROM tableA
EXCEPT SELECT col1, col2 FROM tableB)
SELECT 'tableA' source, * FROM tableA_cte
UNION ALL SELECT 'tableB' source, * FROM tableB_cte
UNION ALL SELECT 'tableC' source, * FROM tableC_cte

Each CTE identifies the distinct rows for each table, the final UNION ALL combines them with the appropriate table source. UNION ALL is fine as the EXCEPT operations will remove duplicate rows.

If this is not what you're looking for you'll have to provide some sample data and the expected output.

You must make sure the column types in each of the tables match. Change the order of the columns from each table so that they match up correctly.

SELECT 'TableA' AS table_name, col1, col2, col3
FROM dbo.TableA
UNION
SELECT 'TableB' AS table_name, col1, col3, col2
FROM dbo.TableB
UNION
SELECT 'TableC' AS table_name, col3, col1, col2
FROM dbo.TableC