Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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