HELP trying to UNION ALL paging results !

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

General SQL Server Forums

Database Design and Application Architecture

HELP trying to UNION ALL paging results !

Author

Topic

futchi
Starting Member

1 Post

Posted - 2010-11-14 : 15:21:54

i am paging resolts of SELECT tbl_users.userName and tbl_thoughts.thought.
i'm tring to get 6 users by page, and 2 thoughts of every user by page. the SELECT queries work separatly ! but tring to UNION them causes "syntex erorr neer UNION statment". what am i doing wrong?

SELECT * FROM (SELECT TOP 6 * FROM (SELECT TOP 6 userName,thought FROM tbl_thoughts INNER JOIN tbl_users ON thoughtUserID = userID WHERE userID=1 ORDER BY userID ASC) AS T1 ORDER BY userID DESC) AS TA1 ORDER BY userID ASC

UNION ALL

SELECT * FROM (SELECT TOP 6 * FROM (SELECT TOP 6 userName,thought FROM tbl_thoughts INNER JOIN tbl_users ON thoughtUserID = userID WHERE userID=2 ORDER BY userID ASC) AS T2 ORDER BY userID DESC) AS TA2 ORDER BY userID ASC";

(i'm using using sql server 2008)

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-14 : 19:48:27

I...just...can't do this anymore

Is there ANY reason why you need to be SO convoluted?

By all means keep it up

And WHO taught you about derived tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-14 : 19:49:17

and you can't do an order by in a sub query I belive

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-15 : 05:21:57

select userName,thought from
(
SELECT TOP 6 1 as sorder,userName,thought FROM tbl_thoughts INNER JOIN tbl_users ON thoughtUserID = userID WHERE userID =1
union all
SELECT TOP 6 2 as sorder,userName,thought FROM tbl_thoughts INNER JOIN tbl_users ON thoughtUserID = userID WHERE userID =1
) as t
ORDER BY sorder,userID ASC

Madhivanan

Failing to plan is Planning to fail

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-15 : 08:48:30

[url]http://www.asp101.com/articles/gal/effectivepaging/default.asp[/url]

I had to do paging for a gridview once, because it was just too slow to use the built-in functionality.

The above link gives you some idea of how to implement it.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-15 : 09:14:21

quote:
Originally posted by madhivanan

select userName,thought from
(
SELECT TOP 6 1 as sorder,userName,thought FROM tbl_thoughts INNER JOIN tbl_users ON thoughtUserID = userID WHERE userID =1
union all
SELECT TOP 6 2 as sorder,userName,thought FROM tbl_thoughts INNER JOIN tbl_users ON thoughtUserID = userID WHERE userID =1
) as t
ORDER BY sorder,userID ASC

Madhivanan

Failing to plan is Planning to fail

??????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-15 : 09:31:42

I guess my point is...

THAT THIS IS MEANINGLESS

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-16 : 03:08:21

select userName,thought from
(
SELECT TOP 6 1 as sorder,userName,thought FROM tbl_thoughts INNER JOIN tbl_users ON thoughtUserID = userID WHERE userID =1 order by userid
) as t
union all
select userName,thought from
(
SELECT TOP 6 1 as sorder,userName,thought FROM tbl_thoughts INNER JOIN tbl_users ON thoughtUserID = userID WHERE userID =2 order by userid
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources