• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Problem with union query containing inner join and top 10 clauses

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
tomfolinsbee
Asked:
tomfolinsbee
  • 4
  • 4
  • 2
  • +2
2 Solutions
 
BillAn1Commented:
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
 
bukkoCommented:
Try it without the 'ORDER BY' clauses.
0
 
davehilditchCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
BillAn1Commented:
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
 
davehilditchCommented:
Oh yeah, sorry I missed his TOP 10 operator.  

Dave Hilditch.
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
Never mind, ignore that last comment.  Not thinking straight.
0
 
tomfolinsbeeAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
BillAn1Commented:
sorry, yes that's exactly what I wanted to say!
in the outer select, the aliass / table names should be dropped
0
 
tomfolinsbeeAuthor Commented:
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
 
BillAn1Commented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now