insert column in union select statement

[BlackMustard]

hi all,

i am currently using the following union select statement to select records from two of my tables to a gridview in asp.net:

SELECT ConcertName AS Name, ConcertDate AS Date, ConcertTime AS Time, ConcertPlace AS Place FROM Concerts UNION SELECT EventName AS Name, EventDate AS Date, EventTime AS Time, EventPlace AS Place FROM Events ORDER BY Date, Time

now i'd like to add two things to this statement:

1. a column that indicates whether the row is taken from the Concerts table or not (i imagine a boolean column with value True if it is, and Null or False if not). I tried with just inserting "True AS InConcerts" after "ConcertPlace AS Place", but of course i got an error.

2. a column to use as "id" column - since ConcertID and EventID (not currently selected) can be the same, i can't use them, but i'd rather need a column containing the row number or something of the sort. i'm not going to use it for anything but selecting, but i would like to be able to choose one row in my gridview and view the details of that record only in a detailsview.

can these two columns be added "on the fly" in sql, or do i have to (i really don't want to!) redesign my database?

thanks!

and by the way, i'm using sql server 2005 express with .net framework 2.0 (maybe soon migrating to 3.5).

[Jim Doherty]

hi all,

i am currently using the following union select statement to select records from two of my tables to a gridview in asp.net:

SELECT ConcertName AS Name, ConcertDate AS Date, ConcertTime AS Time, ConcertPlace AS Place FROM Concerts UNION SELECT EventName AS Name, EventDate AS Date, EventTime AS Time, EventPlace AS Place FROM Events ORDER BY Date, Time

now i'd like to add two things to this statement:

1. a column that indicates whether the row is taken from the Concerts table or not (i imagine a boolean column with value True if it is, and Null or False if not). I tried with just inserting "True AS InConcerts" after "ConcertPlace AS Place", but of course i got an error.

2. a column to use as "id" column - since ConcertID and EventID (not currently selected) can be the same, i can't use them, but i'd rather need a column containing the row number or something of the sort. i'm not going to use it for anything but selecting, but i would like to be able to choose one row in my gridview and view the details of that record only in a detailsview.

can these two columns be added "on the fly" in sql, or do i have to (i really don't want to!) redesign my database?

thanks!

and by the way, i'm using sql server 2005 express with .net framework 2.0 (maybe soon migrating to 3.5).

Hi ya Black Mustard,

I think this is what you require isnt it?

 
SELECT     ConcertName AS Name, ConcertDate AS Date, ConcertTime AS Time, ConcertPlace AS Place, 'Concerts' AS Source
FROM         Concerts
UNION
SELECT     EventName AS Name, EventDate AS Date, EventTime AS Time, EventPlace AS Place, 'Events' AS Source
FROM         Events
ORDER BY Date, Time
 

As for your not being able to use your id numbers I fail to see why? (if I understand you correctly that is) you have a flag marker there telling you the source, so IF logic could be applied to retrieve your records from Concerts if the source field states 'Concerts' conversely selecting records from 'Events' if source states 'Events' in either case using the ID number to identify the relevant table row as part of any WHERE clause in an SQL statement

Regards

Jim :)