For Any Use & On Any Platform
Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.
Course Of The Month
6 days, 2 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
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
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month6 days, 2 hours left to enroll
707 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.