Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

SQl Help - Get Only 1 product

Posted on 2012-12-24

Microsoft SQL Server

1 solution

Medium Priority

263 Views

Last Modified: 2012-12-24

How can I return only the orders that have 1 item with a productid of 46?
This query will return orders that contain multiple products including 46.
I want only orders that have 1 product and that has productid of 46

select *
from Orders a
inner join LineItems b
on a.OrderID = b.OrderID
and b.ProductID = 46
where a.OrderStatusID = 3
and a.GatewaySuccessful = 1

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

Join The Community

2 Comments

LVL 93

Accepted Solution

by:Patrick Matthews

Patrick Matthews earned 2000 total points

ID: 387192482012-12-24

select *
from Orders a
inner join LineItems b
on a.OrderID = b.OrderID
and b.ProductID = 46
where a.OrderStatusID = 3
and a.GatewaySuccessful = 1 and not exists
(select 1
from lineitems b2
where b2.orderid = b.orderid and b2.productid <> 46)

LVL 8

Author Closing Comment

by:JRockFL

ID: 387192592012-12-24

Awesome!! Thank you!!

Featured Post

Question has a verified solution.

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

Detect locking/blocking in SQL and send email alerts

Article by: lcohan

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…