I’m creating a view from 2 tables. I need to create a brand new column in the view; this column must be set to 0 when I’m selecting from the first table, 1 from the second one:
x
Table A:
+---------+---------+
|column A |column B |
+---------+---------+
| Boston | USA |
+---------+---------+
| Chicago | USA |
+---------+---------+
|Las Vegas| USA |
+---------+---------+
Table B:
+---------+---------+
|column A |column B |
+---------+---------+
| Paris | Europe |
+---------+---------+
| Madrid | Europe |
+---------+---------+
| London | Europe |
+---------+---------+
Resulting View:
+---------+---------+---------+
|column A |column B | column C|
+---------+---------+---------+
| Boston | USA | 0 |
+---------+---------+---------+
| Chicago | USA | 0 |
+---------+---------+---------+
|Las Vegas| USA | 0 |
+---------+---------+---------+
| Paris | Europe | 1 |
+---------+---------+---------+
| Madrid | Europe | 1 |
+---------+---------+---------+
| London | Europe | 1 |
+---------+---------+---------+
Advertisement
Answer
Just use a union with a computed column:
SELECT columnA, columnB, 0 AS columnC FROM TableA
UNION ALL
SELECT columnA, columnB, 1 FROM TableB
ORDER BY columnC;