Creating a View question 1

[scoob28]

I have two tables.

TABLE_1 TABLE_2

NUM DATE NUM DATE
--- ------- --- -------
1 1/1/08 1 2/5/08
2 1/5/08 2 2/17/08
3 1/8/08 3 2/13/08

I wish to create a view like this.

NUM ALLDATE DATE_A DATE_B
--- -------- ------- -------
1 1/1/08 1/1/08
1 2/5/08 2/5/08
2 1/5/08 1/5/08
2 2/17/08 2/17/08
3 1/8/08 1/8/08
3 2/13/08 2/13/08

I can do it by creating a temporary table like this but is it possible to do it with a view? I'm on SQL Server 2000.

CREATE TABLE_3
(NUM int, ALLDATE datetime, DATE_A datetime, DATE_B datetime);
GO
INSERT TABLE_3 (NUM, AllDate, DATE_A)
SELECT TABLE_1.NUM, TABLE_1.DATE, TABLE_1.DATE)
FROM TABLE_1;
GO
INSERT TABLE_3 (NUM, AllDate, DATE_B)
SELECT TABLE_2.NUM, TABLE_2.DATE, TABLE_2.DATE)
FROM TABLE_2;
GO
SELECT * FROM TABLE_3
ORDER BY NUM;
GO

 

[gmmastros]

Try this....

Select Num, Date As AllDate, Date As Date_A, NULL As Date_B
From   Table_1

Union All

Select Num, Date As AllDate, NULL As Date_A, Date As Date_B
From   Table_2
Order By Num

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[scoob28]

Thanks gmmastros! That is just what I was looking for. I have a lot to learn and was getting hung up on doing a SELECT NULL as.