Join query criteria

Trying to come up with a query that will find all CustomerIDs from table A where all Items From table B have been shipped or cancelled

from this data query would return CustomerID 123 and 543.  
234 would be excluded because Table B, ID 6  is false for both shipped and cancelled.

SQL 2012
Table B Shipped and Cancelled are both BIT data type

Table A
OrderID  CustomerID
1               123
2               123
3               234
4               234
5               543

Table B
ID   OrderID(FK) Shipped  Cancelled
1     1                        True     False
2     1                        True     False
3     2                        True     False
4     2                        False    True
5     3                        True     False
6     3                        False    False
7     4                        True     False
8     5                        True     False
9     5                        False    True
10   5                        True     False
DON SwiatekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FROM   TableA a INNER JOIN TableB b ON b.OrderID = a.OrderID
WHERE  b.Shipped = 1
OR     b.Cancelled = 1
DON SwiatekAuthor Commented:

This returns 234 when I try it.  Table B row 6 should exclude it.

maybe because other rows in Table B satisfy the WHERE?
Update: Simplified SQL
If you only want customers without any excluded records i.e. 0 orders that are "false for both shipped and cancelled", try:

SELECT c.CustomerID
		SELECT a.CustomerID
				, SUM(CASE WHEN b.Shipped = 0 AND b.Cancelled = 0 THEN 1 ELSE 0 END) AS ExcludedOrders
		FROM   @TableA a INNER JOIN @TableB b ON b.OrderID = a.OrderID
		GROUP BY a.CustomerID
	  ) c
WHERE ExcludedOrders = 0

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark EdwardsChief Technology OfficerCommented:
Yea.  Only customers who have ALL orders where both are false would make sense.  Customer #234 has 1 order that is False/False and one that is True/False.  It's because of the True/False order that you're getting customer #234.

Think through your logic again....
Mark WillsTopic AdvisorCommented:
where ALL orders are either shipped OR cancelled.....

You could check if the condition for exclusion EXISTS

WHERE NOT EXISTS ( SELECT NULL FROM TABLEB B WHERE A.OrderID = B.OrderID AND A.CustomerID = B.CustomerID and B.Shipped = 0 and B.Cancelled = 0)

The good thing about EXISTS is that it stops checking if it finds an instance.

In which case, having a PK, or a secondary index on TABLEB on OrderID,CustomerID and have Shipped and Cacelled as included columns would help a lot.

Because you have ID in the table, I would assume that is an IDENTITY and the PK

So your Secondary index would be

create index idx_tableB_OrderID_CustomerID on tableb (OrderID, CustomerID) include (shipped,Cancelled)

And that way, it should be contained within index
DON SwiatekAuthor Commented:
that works! Thanks!

Mark Wills
I like the idea as it seems more efficient but,
TableB does not contain CustomerID.  If I take out the AND A.CustomerID = B.CustomerID then it returns 234 with 123 and 543.
Mark WillsTopic AdvisorCommented:
Oops.... Sorry about that.

It does need it in the test, and is more efficient... Easy enough to fix :

WHERE NOT EXISTS ( SELECT NULL FROM #TableA A INNER JOIN  #TABLEB B ON A.OrderID = B.OrderID AND A.CustomerID = C.CustomerID and B.Shipped = 0 and B.Cancelled = 0)

Welcome to EE :)
NerdsOfTechTechnology ScientistCommented:
I had a semi-anti-join solution (24ms) up here which looked 'readable' and worked similarly as Mark's solution (Mark's solution is a true semi-anti-join), however, I deleted my comment earlier because of how fast and efficient _agx_'s query was (4ms).!18/26460/26
@DON Swiatek - Do profile both queries.  Mark's suggestion will probably be more efficient with larger volume.. but it's always good to verify it against real data.
awking00Information Technology SpecialistCommented:
with cte as
(select orderid from tablea
 select orderid from tableb where shipped = 0 and cancelled = 0),
cte2 as
(select customerid from tablea where orderid in (select orderid from cte))
select customerid from tablea
select customerid from cte2;
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.