CODEWITHSUNDEEP
sqlsql-server-2012
nckbrz
nckbrz

Reputation: 688

SQL Server 2012, Nested select statement, Syntax error?? I don't see it

It's probably a simple syntax error, but I have been looking at it for an hour and I just can't find it, maybe another set of eyes could help point me in the right direction?? 

select 
    lname +', '+ Fname as "Author Name"
from 
    Author a
join 
    BOOKAUTHOR ba on a.AuthorID = ba.authorid
where 
    ba.isbn =  (select ba.isbn  
                from
                    (select 
                        ROW_NUMBER() over (order by sum(quantity) desc) as "Placement", 
                        b.isbn,
                        sum(QUANTITY) as "Total"
                     from 
                        ORDERITEMS oi
                     join 
                        Books b on b.ISBN = oi.isbn
                     group by 
                        b.ISBN)
                where Placement = 1) --the "WHERE" is giving me an error.

Upvotes: 0

Views: 817

Answers (1)

Michael Liu
Michael Liu

Reputation: 55339

When your FROM clause specifies a derived table as the table source, you must give the subquery an alias:

select lname +', '+ Fname as "Author Name"
from Author a
join BOOKAUTHOR ba on
a.AuthorID = ba.authorid
where ba.isbn = 
       (select q.isbn -- <== notice the "q"
        from
            (select ROW_NUMBER() 
            over (order by sum(quantity) desc) as "Placement", 
            b.isbn,
            sum(QUANTITY) as "Total"
            from ORDERITEMS oi
            join Books b
            on b.ISBN = oi.isbn
            group by b.ISBN) q -- <== notice the "q"
        where Placement = 1)

From the MSDN Library documentation:

[ FROM { <table_source> } [ ,...n ] ] 
<table_source> ::= 
{
    ...
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
    ...
}

Upvotes: 4

Related Questions