[rastaman832003]

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).

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	

[JasonMcAuley]

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 April 2008 - 08:24 AM

[rastaman832003]

JasonMcAuley, on 11 Apr, 2008 - 07:30 AM, said:

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.

[JasonMcAuley]

How big are your result sets?

[rastaman832003]

JasonMcAuley, on 11 Apr, 2008 - 08:53 AM, said:

How big are your result sets?

anywhere from 10,000 to 100,000(roughly) depending on what Server its run on.

[JasonMcAuley]

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.

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 April 2008 - 09:01 AM

[baavgai]

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.

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:

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

Hope this helps.

[rastaman832003]

baavgai, on 11 Apr, 2008 - 09:06 AM, said:

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 April 2008 - 11:39 AM

[baavgai]

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:

SELECT RRID,SkillID,UserID,SubmittedOn,Explanation,LastWorkDay,Signature,WithdrawnOn,WithdrawSignature FROM Employee.RN
UNION
SELECT RRID,SkillID,UserID,SubmittedOn,Explanation,LastWorkDay,Signature,WithdrawnOn,WithdrawSignature FROM History.ERN

[rastaman832003]

Worked out finally. Thanks for the help all, i appreciate it.