?
Solved

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

Posted on 2012-12-24
5
Medium Priority
?
283 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

752 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