SQL Subquery

I could really use some assistance re configuring the query below.

I am trying to bring back data for the "Invtid" which will show the last "Invcdate" and the "ShipperID" which in was invoiced on

Query:
select i.invtid, i.Descr,
      (select top (1) b.InvcDate--,a.ShipperID
      from SOShipLine a, SOShipHeader b
      where a.shipperid=b.shipperid  
      and b.SOTypeID in ('CS','SO')
      and a.InvtID=i.invtid
      order by a.ShipperID desc)
from inventory i
order by i.invtid

I have commented out the --,a.ShipperID for the purposes of getting the query to run with just the Invcdate getting return but I could really use the ShipperId.

When I don't comment it out I get the following error:"Msg 116, Level 16, State 1, Line 8
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

your assistance will be greatly appreicated.
armgonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Please try using an OUTER APPLY instead
SELECT
      i.invtid
    , i.Descr
    , oa.InvcDate
    , oa.ShipperID
FROM inventory i
OUTER APPLY (
            SELECT TOP (1)
                  b.InvcDate
                , a.ShipperID
            FROM SOShipLine a
               INNER JOIN SOShipHeader b ON a.shipperid = b.shipperid
                                        AND b.SOTypeID IN ('CS', 'SO')
            WHERE a.InvtID = i.invtid
            ORDER BY a.ShipperID DESC
            ) OA 
ORDER BY i.invtid
;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
>>"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

This message simply means you cannot have more than one column and one row, as a result from the subquery.

Imagine you have a very simple query like this: select id from table1 and the entire result looks like this
ID
1
2
3
4
5

Now imagine trying to do this instead

select (select id from table1) as id from table1

Here you would be attempting to force each "cell" of the result to hold 5 rows and SQL just won't do that. The same is true  of columns, you cannot force multiple columns into  single output column unless you use concatenation or some technique like xml.

so that is why it works when you comment out the second column.

It is different when you use the query within an APPLY operator, the restriction on the number of columns is removed, so you can get both of the wanted columns that way.
PortletPaulEE Topic AdvisorCommented:
>> last "Invcdate" and the "ShipperID"

Then you should: ORDER BY  b.InvcDate DESC, a.ShipperID
PortletPaulEE Topic AdvisorCommented:
and, here is an alternative method that uses ROW_NUMBER() to locate just the most recent rows. I would probably use this technique myself but an apply operator also works.
SELECT
      i.invtid
    , i.Descr
    , d.InvcDate
    , d.ShipperID
FROM inventory i
LEFT JOIN (
            SELECT
                  a.InvtID
                , b.InvcDate
                , a.ShipperID
                , ROW_NUMBER() OVER(PARTITION BY a.InvtID
                                    ORDER BY b.InvcDate DESC, a.ShipperID) as rn
            FROM SOShipLine a
               INNER JOIN SOShipHeader b ON a.shipperid = b.shipperid
                                        AND b.SOTypeID IN ('CS', 'SO')
            ) d
                ON i.invtid = d.invtid
               AND d.RN = 1
ORDER BY i.invtid
;

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.