Union All join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Union All join

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2012-04-16 : 12:15:36

Hi,
I have two queries that I need to get in one SQL view but because there is no logical way to join the tables I need another solution.

I found the "Union All" option and this might work for my but I need to order the one view that I get from both queries by their createtime but I don’t think I can do this

These quires below get data but when I try and add “order by a.createtime, h.createtime desc” to both quires I get an error and when I add it to the last part of the query it doesn’t like that either.

The question is, am I going the right way about this? Can I get it to show the latest rows from both tables in one view?

Thanks for looking.


select top 10 c.uniqueid, contact, company, subject, h.notes, h.createtime from wce_history h join 
wce_linkto l on h.uniqueid = l.luniqueid left join 
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001'
union all
select top 10 c.uniqueid, contact, company, subject, a.notes, a.createtime from wce_activity a join 
wce_linkto l on a.uniqueid = l.luniqueid left join 
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 12:23:33

you need the order by applied only for first resultset?

if not you can just give

order by notes, createtime

to end of statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mshsilver
Posting Yak Master

112 Posts

Posted - 2012-04-16 : 12:32:08

Thanks for your reply.

When i try order by createtime desc i get the following error becuase there are createtime fields in more than one table in each query.


select top 10 c.uniqueid, contact, company, subject, h.notes, createtime from wce_history h join 
wce_linkto l on h.uniqueid = l.luniqueid left join 
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001'
union all
select top 10 c.uniqueid, contact, company, subject, a.notes, createtime from wce_activity a join 
wce_linkto l on a.uniqueid = l.luniqueid left join 
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001' order by createtime desc

Error:
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'createtime'.
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'createtime'.

So i have to specify h.createtime and a.createtime but when i do the following query i get teh following error.


select top 10 c.uniqueid, contact, company, subject, h.notes, h.createtime from wce_history h join 
wce_linkto l on h.uniqueid = l.luniqueid left join 
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001'
union all
select top 10 c.uniqueid, contact, company, subject, a.notes, a.createtime from wce_activity a join 
wce_linkto l on a.uniqueid = l.luniqueid left join 
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001' order by h.createtime, a.createtime desc

Error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.createtime" could not be bound.
Msg 104, Level 16, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Is there an alternative?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-16 : 14:14:27

Use only aliases from the first part of the UNION ALL in your order by clause as in:

order by h.createtime desc

That will order all the results including those from the select after the UNION ALL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 14:42:09

quote:
Originally posted by mshsilver

Thanks for your reply.

When i try order by createtime desc i get the following error becuase there are createtime fields in more than one table in each query.

select top 10 c.uniqueid, contact, company, subject, h.notes, createtime from wce_history h join
wce_linkto l on h.uniqueid = l.luniqueid left join
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001'
union all
select top 10 c.uniqueid, contact, company, subject, a.notes, createtime from wce_activity a join
wce_linkto l on a.uniqueid = l.luniqueid left join
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001' order by createtime desc

Error:
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'createtime'.
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'createtime'.

So i have to specify h.createtime and a.createtime but when i do the following query i get teh following error.

select top 10 c.uniqueid, contact, company, subject, h.notes, h.createtime from wce_history h join
wce_linkto l on h.uniqueid = l.luniqueid left join
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001'
union all
select top 10 c.uniqueid, contact, company, subject, a.notes, a.createtime from wce_activity a join
wce_linkto l on a.uniqueid = l.luniqueid left join
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001' order by h.createtime, a.createtime desc

Error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.createtime" could not be bound.
Msg 104, Level 16, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Is there an alternative?

do you mean this?


select top 10 c.uniqueid, contact, company, subject, h.notes, h.createtime as createtime  from wce_history h join 
wce_linkto l on h.uniqueid = l.luniqueid left join 
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001'
union all
select top 10 c.uniqueid, contact, company, subject, a.notes, a.createtime from wce_activity a join 
wce_linkto l on a.uniqueid = l.luniqueid left join 
wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001' 
order by createtime desc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mshsilver
Posting Yak Master

112 Posts

Posted - 2012-04-17 : 04:58:43

I have found the issue but still need some help.

The "top 10" is randomly taking the top 10 results from both tables and then sorting them by date which is why i was confused i thought it would sort by date then take the top 10 givgin me the most recent, not the most recent of a random number of results.

Is tehre away to get the most recent dates then take the top x results from that?

Thanks for looking.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-17 : 06:46:24

You can make the original query into a subquery or CTE, like this:

SELECT TOP 10 * FROM
(
   --- your original query here without the order by clause
)S
ORDER BY createtime DESC;

mshsilver
Posting Yak Master

112 Posts

Posted - 2012-04-17 : 08:25:11

That is great thank you, i was not aware you could do that. There is however an issue with what I want to achieve here but logically thinking it through i don't think i am going to be able to achieve what i need with this method or any for that matter.

It is now giving me the top 10 rows but the rows (and for most normal situations this query you provided does work), I however, would like to see in the results, the results from the second query which are from a few years back so i want an amalgamation of the two separate queries and for the select top to look at both result sets instead of just the one with the most recent dates.

The idea is you will see the top of the first query x rows and the top of the second query x rows so even if the rows in the second query were dated years ago and the first query were in recent years you would see the first query rows first and then the second query rows.

I can extend the top to show many thousands of rows which encapsulates everything but then I will get a performance issue and it won’t be neat and tidy, hence only trying to show the top ten.

Does that make sense? And do you think I am dreaming to get the results that I need?

Thanks again for your help it is good to talk it through.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-17 : 09:12:08

I didn't quite follow what you are trying to do, but if you want to select top 10 from each query and union them you could do this:

SELECT * FROM
(
	SELECT TOP 10 c.uniqueid, contact, company, subject, h.notes, h.createtime from wce_history h join 
	wce_linkto l on h.uniqueid = l.luniqueid left join 
	wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001'
	ORDER BY h.createtime DESC
) S1

union ALL

SELECT * FROM 
(
	select TOP 10 c.uniqueid, contact, company, subject, a.notes, a.createtime from wce_activity a join 
	wce_linkto l on a.uniqueid = l.luniqueid left join 
	wce_contact c on l.lentityid = c.uniqueid where c.uniqueid = '000000000001'
	ORDER BY a.createtime DESC
) S2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources