[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQl Help - Get Only specific product id and other products in stock.

Posted on 2012-12-24
5
Medium Priority
?
285 Views
Last Modified: 2012-12-25
ProductID 11 is currently out of stock.
It has a quntity of 0

I need to get the orders that have productid = 11
and only other products that are in stock.

Here is what I'm using, but it is returning products that have a quantity of 0.

select *
from Orders a
inner join LineItems b
on a.OrderID = b.OrderID
and b.ProductID = 11
where a.OrderStatusID = 3
and a.GatewaySuccessful = 1 and not exists
    (select 1
    from Products p
    where b.ProductID = p.ProductID
    and p.QuantityInStock > 0)
0
Comment
Question by:JRockFL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 38719382
I would rework this with an inner join instead of the not exists clause. Also, unless you need all of those columns I would list them individually instead of using the select *.

select *
from Orders a
inner join LineItems b on a.OrderID = b.OrderID
inner join Products p on  b.ProductID = p.ProductID
where a.OrderStatusID = 3
and b.ProductID = 11
and a.GatewaySuccessful = 1
and p.QuantityInStock > 0
0
 
LVL 8

Author Comment

by:JRockFL
ID: 38719390
Thank you for the reply.
That does not return any results though. Same issue I was having.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 38719654
If you need "to get the orders that have productid = 11
and only other products that are in stock", try using an OR clause. This should return anything that has 11 as a productID or quantityStock > 0.

select *
from Orders a
inner join LineItems b on a.OrderID = b.OrderID
Inner join Products p on  b.ProductID = p.ProductID
where a.OrderStatusID = 3
and (b.ProductID = 11
or p.QuantityInStock > 0)
and a.GatewaySuccessful = 1
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38719960
you want orders that have product 11, and return all products for those orders that are NOT 0:

select *
from Orders a
  join LineItems b
    on b.OrderID = a.OrderID
  join Products p
    on p.ProductID = b.ProductID
  and p.QuantityInStock > 0
where exits ( select null from  LineItems p11
          where p11.OrderID = a.OrderID 
   amd p11.ProductID= 11
  
WHERE a.OrderStatusID = 3
   and a.GatewaySuccessful = 1 

Open in new window

0
 
LVL 8

Author Closing Comment

by:JRockFL
ID: 38720043
Thank you!
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

649 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