May 22, 2008 at 11:30 am
Query 1
select StartTime,Department,JobNumber from Emp1
union all
select StartTime,DepartmentDescript,JobNumberDescription,Counter,OtherID from Emp
Query 2
select regt from (select * from Emp union all select * from Emp1)as R where regt is not null and regt<>0 and punchtype in ('C','E')
union all
select ot from (select * from Emp union all select * from Emp1) as R where ot is not null and ot<>0 and punchtype in('C','E')
union all
select dt from (select * from Emp union all select * from Emp1) as R where dt is not null and dt<>0 and punchtype in('C','E')
union all
select totaltime from (select * from Emp union all select * from Emp1)as R where totaltime is not null and dt<>0 and not punchtype in('C','E')
How can i get a combined resultset for these 2 queries.
May 22, 2008 at 1:06 pm
In order to use the UNION on a select. The selects must have the same amount of fields. If some don't then use something like this.
select 'A','B','C'
union
select 'A','B',''
May 22, 2008 at 1:12 pm
As rroque has stated, queries in a UNION/UNION ALL must have the same number and types of columns in the SELECT. So the UNION ALL in your first query should generate an error.
Outside of that, if you need additional help, you'll want to give us more details including sample data and expected results.
May 23, 2008 at 6:05 am
Since these are all pulling from the same table (except one of the queries) have you looked at either using an OUTER join or even an INNER join to pull this data together? In some cases breaking it all down like this might perform better, but in other cases putting it all together will. I'd sure try doing this as a series of JOINS and then compare the scans & reads to what you have now.
Plus, that makes it easier to turn these into a single result set.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply