Join 150,428 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,078 people online right now. Registration is fast and FREE... Join Now!
This is my first question so here we go... I need to Union 2 select statements(each from a different table of course) and then i would like to be able to select from that Union like its a table. In my code i tried to name the Unioned Select, but when it runs i get the error that it doesnt recognize the name i gave it (RUnion).
CODE
SELECT SubmittedOn, Explanation, LastWorkDay, Signature, WithdrawnOn, WithdrawSignature FROM Employee.RN UNION SELECT SubmittedOn, Explanation, LastWorkDay, Signature, WithdrawnOn, WithdrawSignature FROM History.ERN AS RUnion
SELECT EU.UserID as UserID, EU.Username as Username, SS.Name as Skill, LRR.Description as Description, RU.SubmittedOn as SubmittedOn, RU.Explanation as Explanation, RU.LastWorkDay as LastWorkDay, RU.Signature as Signature, RU.WithdrawnOn as WithdrawnOn, RU.WithdrawSignature as WithdrawSignature FROM RUnion as RU JOIN Employee.U EU WITH (NOLOCK) on EU.UserID = RU.UserID JOIN Skill.S SS WITH (NOLOCK) on SS.SkillID = RU.SkillID JOIN Lookup.RR LRR WITH (NOLOCK) on LRR.RRID = RU.RRID WHERE RU.UserID = @userID
Insert your unioned tables into a temp table, select from your temp table, then drop your temp table.
Edit: I know some people cringe when they hear temporary tables. I've heard there is a good substitute to using them; but I don't know a whole lot about it. If any SQL Gurus out there can enlighten me; please do. However, in the case of what you are doing rastaman; temp tables will be sufficient.
This post has been edited by JasonMcAuley: 11 Apr, 2008 - 07:24 AM
Insert your unioned tables into a temp table, select from your temp table, then drop your temp table.
Edit: I know some people cringe when they hear temporary tables. I've heard there is a good substitute to using them; but I don't know a whole lot about it. If any SQL Gurus out there can enlighten me; please do. However, in the case of what you are doing rastaman; temp tables will be sufficient.
The reason i am doing this the way i am is to reduce overhead in the running and from what i understand Temp Tables use alot of overhead when they are used. I am not great with SQL so i dont know, but thats what i had heard.
Now I recall something I have done before... You can also use table variables. Table variables supposedly use less overhead. Here is an example of table variable declaration.
CODE
DECLARE @people TABLE ( id INT, name VARCHAR(32) )
Test this by grabbing a huge result set using both a temp table and variable table and comparing the execution times.
This post has been edited by JasonMcAuley: 11 Apr, 2008 - 08:01 AM
Just bundle it up as a subquery. Should work fine. Depending on the intelligence of your parser, this should be faster than a temp table because it should take advantage of filters applied.
CODE
SELECT EU.UserID as UserID, EU.Username as Username, SS.Name as Skill, LRR.Description as Description, RU.SubmittedOn as SubmittedOn, RU.Explanation as Explanation, RU.LastWorkDay as LastWorkDay, RU.Signature as Signature, RU.WithdrawnOn as WithdrawnOn, RU.WithdrawSignature as WithdrawSignature FROM ( SELECT SubmittedOn,Explanation,LastWorkDay,Signature,WithdrawnOn,WithdrawSignature FROM Employee.RN UNION SELECT SubmittedOn,Explanation,LastWorkDay,Signature,WithdrawnOn,WithdrawSignature FROM History.ERN ) as RU JOIN Employee.U EU WITH (NOLOCK) on EU.UserID = RU.UserID JOIN Skill.S SS WITH (NOLOCK) on SS.SkillID = RU.SkillID JOIN Lookup.RR LRR WITH (NOLOCK) on LRR.RRID = RU.RRID WHERE RU.UserID = @userID
For added query goodness, you may have better results if you combine the final resultsets. It really depends on how gracious the parser is being. You'll have to pass the id twice for this one:
CODE
SELECT EU.UserID as UserID, EU.Username as Username, SS.Name as Skill, LRR.Description as Description, R.SubmittedOn as SubmittedOn, R.Explanation as Explanation, R.LastWorkDay as LastWorkDay, R.Signature as Signature, R.WithdrawnOn as WithdrawnOn, R.WithdrawSignature as WithdrawSignature FROM Employee.RN R JOIN Employee.U EU WITH (NOLOCK) on EU.UserID = R.UserID JOIN Skill.S SS WITH (NOLOCK) on SS.SkillID = R.SkillID JOIN Lookup.RR LRR WITH (NOLOCK) on LRR.RRID = R.RRID WHERE R.UserID = @userID1 UNION SELECT EU.UserID as UserID, EU.Username as Username, SS.Name as Skill, LRR.Description as Description, R.SubmittedOn as SubmittedOn, R.Explanation as Explanation, R.LastWorkDay as LastWorkDay, R.Signature as Signature, R.WithdrawnOn as WithdrawnOn, R.WithdrawSignature as WithdrawSignature FROM History.ERN R JOIN Employee.U EU WITH (NOLOCK) on EU.UserID = R.UserID JOIN Skill.S SS WITH (NOLOCK) on SS.SkillID = R.SkillID JOIN Lookup.RR LRR WITH (NOLOCK) on LRR.RRID = R.RRID WHERE R.UserID = @userID2
I tried it the first way but it wont let me join anything to the RU variable that isnt in the select statement inside the from(). Says its an invalid column name.
Example: JOIN Employee.U EU WITH (NOLOCK) on EU.UserID = RU.UserID<--Invalid JOIN Skill.S SS WITH (NOLOCK) on SS.SkillID = RU.SkillID<---Invalid JOIN Lookup.RR LRR WITH (NOLOCK) on LRR.RRID = RU.RRID<---Invalid
This post has been edited by rastaman832003: 11 Apr, 2008 - 10:39 AM
Indeed. I hadn't noticed the key fields were omitted from the original union. You kind of need those as part of the result set.
So, include em:
CODE
SELECT RRID,SkillID,UserID,SubmittedOn,Explanation,LastWorkDay,Signature,WithdrawnOn,Wi thdrawSignature FROM Employee.RN UNION SELECT RRID,SkillID,UserID,SubmittedOn,Explanation,LastWorkDay,Signature,WithdrawnOn,Wi thdrawSignature FROM History.ERN
| 