• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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)
0
JRockFL
Asked:
JRockFL
  • 2
  • 2
1 Solution
 
Jerry MillerCommented:
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
 
JRockFLAuthor Commented:
Thank you for the reply.
That does not return any results though. Same issue I was having.
0
 
Jerry MillerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
JRockFLAuthor Commented:
Thank you!
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now