September 15, 2017 at 12:51 pm
I'm trying to return the orders and order line items for a specified customer.
How can I get the orderid from the previous query?
select *
from orders
where customerid = @CustomerID
select *
from lineitems
where orderid = orderid.from.previousquery
September 15, 2017 at 1:31 pm
jason 50597 - Friday, September 15, 2017 12:51 PMHow can I get the orderid from the previous query?
The first query can return multiple orderid values, as I assume a customer can have many orders over time. If you want all the items from all the orders then maybe join the tables together on orderid then you can filter on orders table and return all the columns you want from lineitmes.
If you only wanted 1 specific orderid then you'd need some more clarification about which order of the customer you want (e.g. most recent order)
September 15, 2017 at 1:58 pm
We usually do this with joins.
select l.*
from lineitems l
inner join orders o
on o.orderid = l.orderid
where customerid = @customerid
September 21, 2017 at 8:12 am
Or:
select *
from lineitems
where orderid in (select orderid from orders where customerid = @CustomerID)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply