Link to home
Start Free TrialLog in
Avatar of JRockFL
JRockFLFlag for United States of America

asked on

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

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)
Avatar of Jerry Miller
Jerry Miller
Flag of United States of America image

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
Avatar of JRockFL

ASKER

Thank you for the reply.
That does not return any results though. Same issue I was having.
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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JRockFL

ASKER

Thank you!