troycomp
asked on
Sql query to certain data only (no mix and match)
I have attached my database diagram. Very simple structure. I need to write a query to display only orders who have ordered a certain product only. To be specific, Halloween products are to only show. So if Jane ordered 2 Halloween products, see should show in my query. If Jill ordered 4 Halloween products and 1 Christmas product, she should not show in my query. I Think i'm close, but I need assistance. Heres my query:
select orderid
FROM orderheader
where isfulfilled = 0 and inprocess=0
and OrderId in (
SELECT oh.orderid FROM
OrderHeader oh
inner join OrderLines ol on oh.OrderId = ol.OrderId
WHERE oh.OrderId in (SELECT OrderId from OrderLines group by OrderId having COUNT(*) > 0)
and ol.ProductId > 225)
In my database, all Halloween products have order ids greater than 225. Thanks
Capture.JPG
select orderid
FROM orderheader
where isfulfilled = 0 and inprocess=0
and OrderId in (
SELECT oh.orderid FROM
OrderHeader oh
inner join OrderLines ol on oh.OrderId = ol.OrderId
WHERE oh.OrderId in (SELECT OrderId from OrderLines group by OrderId having COUNT(*) > 0)
and ol.ProductId > 225)
In my database, all Halloween products have order ids greater than 225. Thanks
Capture.JPG
Sorry I missed the WHERE clause when I copy/paset:
select orderid
FROM orderheader oh
inner join OrderLines ol on oh.OrderId = ol.OrderId and ol.ProductId > 225
where oh.isfulfilled = 0 and oh.inprocess=0
select orderid
FROM orderheader oh
inner join OrderLines ol on oh.OrderId = ol.OrderId and ol.ProductId > 225
where oh.isfulfilled = 0 and oh.inprocess=0
ASKER
@lcohan
Thanks for responding, but keep in mind i only want to show orders that have halloween products in them, not just orders with halloween combined with other products. Your query is like mine
Thanks for responding, but keep in mind i only want to show orders that have halloween products in them, not just orders with halloween combined with other products. Your query is like mine
Try this
Looks like code did not attach
select orderid
FROM orderheader
where isfulfilled = 0 and inprocess=0
and OrderId in (
SELECT oh.orderid FROM
OrderHeader oh
inner join OrderLines ol on oh.OrderId = ol.OrderId
WHERE oh.OrderId in (SELECT OrderId from OrderLines group by OrderId having COUNT(*) > 0)
and ol.ProductId > 225)
and OrderId not in (
SELECT oh.orderid FROM
OrderHeader oh
inner join OrderLines ol on oh.OrderId = ol.OrderId
WHERE oh.OrderId in (SELECT OrderId from OrderLines group by OrderId having COUNT(*) > 0)
and ol.ProductId < 226)
<<In my database, all Halloween products have order ids greater than 225. Thanks >>
OrderID's or rather ProductID's > 255??
If ProductId's then I believe the query I sent does just that - it will filter from orderheader all orderid's that are in OrderLines ol on oh.OrderId = ol.OrderId AND the ol.ProductId > 225
and of course
where oh.isfulfilled = 0 and oh.inprocess=0
OrderID's or rather ProductID's > 255??
If ProductId's then I believe the query I sent does just that - it will filter from orderheader all orderid's that are in OrderLines ol on oh.OrderId = ol.OrderId AND the ol.ProductId > 225
and of course
where oh.isfulfilled = 0 and oh.inprocess=0
try this
select orderid from (
select orderid, sum(case when ProductID>255 then 1 else 0 end) cntGT255, count(1) cnt
from orderLines o
group by orderid
) x where cnt=cntGT255
above query will give you all orders which contains only Halloween products in it... is this what you want?
ASKER
@vincem1099
That worked. This is the most congested sql query i've ever seen. What magic did you add to my original query to make it work? I can see what you did but i cant explain it.
That worked. This is the most congested sql query i've ever seen. What magic did you add to my original query to make it work? I can see what you did but i cant explain it.
I used your existing subquery that had all holloween products to show all orders without halloween products and used it to not include orders that matched. I agree it can probably be optimized better
did you try mine @ 36932303?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> it should do the same thing
Though you may need to add back the the extra filters on isfulfilled and inprocess
Though you may need to add back the the extra filters on isfulfilled and inprocess
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the delay...i went to lunch and then got caught up here at work:
@HainKurt - your query worked fine
@vincem1099 - yours did too, the simplified version is much better
@_aqx- thanks for your input as well
I have implemented vincem1099 query but i will keep HainKurt's as well. Everyone gets points. Thank you so much. I learned something today
@HainKurt - your query worked fine
@vincem1099 - yours did too, the simplified version is much better
@_aqx- thanks for your input as well
I have implemented vincem1099 query but i will keep HainKurt's as well. Everyone gets points. Thank you so much. I learned something today
Thanks, but not for me :) @HainKurt and @vincem1099 did all the work. I just added a small fyi comment.
select orderid
FROM orderheader oh
inner join OrderLines ol on oh.OrderId = ol.OrderId and ol.ProductId > 225