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)
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)
Microsoft SQL Server
Last Comment
JRockFL
ASKER
Thank you for the reply.
That does not return any results though. Same issue I was having.
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you!
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