?
Solved

Sql query to certain data only (no mix and match)

Posted on 2011-10-07
17
Medium Priority
?
215 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:troycomp
  • 4
  • 4
  • 3
  • +2
17 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 36932186
I think this should be just like below:

select orderid
FROM orderheader oh
      inner join OrderLines ol on oh.OrderId = ol.OrderId and ol.ProductId > 225
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36932192
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
0
 

Author Comment

by:troycomp
ID: 36932224
@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
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 7

Expert Comment

by:vincem1099
ID: 36932236
Try this
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 36932245
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)

Open in new window

0
 
LVL 40

Expert Comment

by:lcohan
ID: 36932297
<<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
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36932303
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

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36932310
above query will give you all orders which contains only Halloween products in it... is this what you want?
0
 

Author Comment

by:troycomp
ID: 36932379
@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.
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 36932410
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
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36932617
did you try mine @ 36932303?
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 200 total points
ID: 36932811
@troycomp - I would try HainKurt's suggestion as it should do the same thing more efficiently. But if you're curious about the other query it basically says

SELECT orderid
FROM    orderheader
WHERE  isfulfilled = 0 and inprocess=0
AND       OrderId in (  ... orders that contain halloween orders only ... )
AND       OrderId NOT in ( ...orders that contain NON-halloween items ...)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36932825
    >> it should do the same thing

Though you may need to add back the the extra filters on isfulfilled  and inprocess

0
 
LVL 7

Accepted Solution

by:
vincem1099 earned 1000 total points
ID: 36932890
Here is a simplified version of what I did earlier
select orderid 
FROM  orderheader 
where isfulfilled = 0 and inprocess=0
and OrderId in (
SELECT orderid FROM 
OrderLines 
WHERE ProductId > 225) 
and OrderId not in (
SELECT orderid FROM 
OrderLines 
WHEREProductId < 226)

Open in new window

0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 800 total points
ID: 36932943
this is the query with where condition is added
select oh.orderid from (
select orderid, sum(case when ProductID>255 then 1 else 0 end) cntGT255, count(1) cnt
  from orderLines
 group by orderid
) x inner join orderHeader oh on x.orderid=oh.orderid
where cnt=cntGT255 and oh.isfulfilled = 0 and oh.inprocess=0

Open in new window

0
 

Author Comment

by:troycomp
ID: 36933026
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
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36933045
Thanks, but not for me :)  @HainKurt  and @vincem1099 did all the work. I just added a small fyi comment.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question