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
COURSE of the MONTH
12 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQl Help - Get Only specific product id and other products in stock.
Posted on 2012-12-24
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)
[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
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
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
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
Active 1 day ago
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
Featured Post
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
Course of the Month12 days, 4 hours left to enroll
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.