?
Solved

Problem with union query containing inner join and top 10 clauses

Posted on 2004-08-23
13
Medium Priority
?
453 Views
Last Modified: 2012-05-05
I'm trying to write a query to fetch 10 records of each DocTypeID. The first Select statement works fine. When I add the union clause and a second select statement with a different DocTypeID, I get an error about syntax error near Union clause.

I understand that a union query is should only have ORDER BY in the last clause, but then the first clause wouldn't know which field to sort the top 10 records.

Here's the query:

SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3) ORDER BY Docs.ID DESC  

UNION

SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 4) ORDER BY Docs.ID DESC

Thanks!

Tom  
0
Comment
Question by:tomfolinsbee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 17

Expert Comment

by:BillAn1
ID: 11874248
You will need to do something like this  - encapsulate the two sub-queries, then select * from both, and order the global result set. You can use a dummy field called sec to keep the two halves seperate if that is what you need :

select Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM
(
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 1 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3) ORDER BY Docs.ID DESC  
) a
UNION
select Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM (
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 2 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 4) ORDER BY Docs.ID DESC
) b
order by sec, ID desc
0
 
LVL 8

Expert Comment

by:bukko
ID: 11875431
Try it without the 'ORDER BY' clauses.
0
 
LVL 4

Expert Comment

by:davehilditch
ID: 11875920
Take out ORDER BY in the *first* part of the UNION and it will work.  You can keep your second ORDER BY clause which actually will order the entire result set.

Dave Hilditch.
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 17

Expert Comment

by:BillAn1
ID: 11876052
No, if you just take away the order by from the two halves (or the first), you wont get the correct top 10 in each half You will simply get the first 10 records that SQL Server happens to find. You need to keep the "order by" , but isolate it inside a nested query.
0
 
LVL 4

Expert Comment

by:davehilditch
ID: 11876118
Oh yeah, sorry I missed his TOP 10 operator.  

Dave Hilditch.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11877519
I agree with BillAn1, but in addition you should change: UNION to UNION ALL or you could potentially end up with less than 20 (if there are duplicates).  It is also more efficient.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11877549
Somebody correct me if if I am wrong, but this should also work:

SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3)
UNION ALL
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 4) ORDER BY Docs.ID DESC

ORDER BY Docs.DocTypeID, Docs.ID DESC  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11877558
Never mind, ignore that last comment.  Not thinking straight.
0
 

Author Comment

by:tomfolinsbee
ID: 11877568
Hello,

I tried BillAn's suggestion and I get this error message:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.

This part of the query works fine:
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3)
ORDER BY Docs.ID DESC

Any idea why I'm getting this error?

Tom
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 11877699
I suspect somehting like this was intended (I also replace UNION with UNION ALL):

select ID, Code, DocType, Title, Posted, DownloadTime
FROM
(
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 1 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3) ORDER BY Docs.ID DESC  
) a
UNION ALL
select Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM (
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 2 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 4) ORDER BY Docs.ID DESC
) b
order by sec, ID desc
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11878895
sorry, yes that's exactly what I wanted to say!
in the outer select, the aliass / table names should be dropped
0
 

Author Comment

by:tomfolinsbee
ID: 11884160
Thanks!

I tried it and now get this message:

Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION operator.

So I put "sec" in the first row and now get these errors:


Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'DocTypes' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.

Really appreciate everyone's help with this.
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 1000 total points
ID: 11884298
for both halves of the query you need to drop the aliases :

select ID, Code, DocType, Title, Posted, DownloadTime,sec
FROM
(
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 1 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3) ORDER BY Docs.ID DESC  
) a
UNION ALL
select ID, Code, DocType, Title, Posted, DownloadTime, sec
FROM (
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 2 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 4) ORDER BY Docs.ID DESC
) b
order by sec, ID desc
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question