sql statement help needed

[bbolte]

ok, this is for a class and i've been working on this for 2 hours. here is the data i need to find: "List the entertainers who played engagements for customer Bonnicksen but did not play any engagements for Rosales."

here is my sql statement: SELECT bonnickson.EntStageName
FROM
[SELECT DISTINCT Entertainers.EntStageName
FROM (Entertainers
INNER JOIN Engagements ON Engagements.EntertainerID = Entertainers.EntertainerID)
INNER JOIN Customers ON Engagements.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID = 10011]. AS bonnickson
LEFT OUTER JOIN
[SELECT DISTINCT Entertainers.EntStageName
FROM (Entertainers
INNER JOIN Engagements ON Engagements.EntertainerID = Entertainers.EntertainerID)
INNER JOIN Customers ON Engagements.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID = 10014]. AS rosales
ON bonnickson.EntStageName = rosales.EntStageName

this statement returns 7 rows, i believe the answer should be 3 rows. i did each inner select by themselves and compared, their were only 3 that never played for rosales. this is an access db. can anyone see from this where my mistake is or do you need to see more info?

i have some screen shots posted here

[DaveMaxwell]

I think you just made it to hard on yourself. Try this:

SELECT DISTINCT EntStageName
  FROM ENTERTAINERS E,
       ENGAGEMENTS  N
 WHERE E.EntertainerID = N.EntertainerID
   AND N.CustomerID IN (SELECT CustomerID
                          FROM Customers
                         WHERE CustomerName = 'Bonnickson')
   AND N.CustomerID NOT IN (SELECT CustomerID
                              FROM Customers
                             WHERE CustomerName = 'Rosales')

That should get you what you need. I noticed you cheated and hardcoded the customerIDs in your joins which your prof probably wouldn't like, BUT if you could do it that way then change the select to this:

SELECT DISTINCT EntStageName
  FROM ENTERTAINERS E,
       ENGAGEMENTS  N
 WHERE E.EntertainerID = N.EntertainerID
   AND N.CustomerID = 10011
   AND N.CustomerID <> 10014

[bbolte]

thanks, i will give it a try after a bit. got to get some work done first.

[bbolte]

ok, the first one gave me an error, the second returned 7 rows. it still didn't strip out those that performed for both customers. thanks though...

[bbolte]

played with the first one some and changed it to this:

SELECT DISTINCT EntStageName
FROM ENTERTAINERS E, ENGAGEMENTS N
WHERE E.EntertainerID = N.EntertainerID AND N.CustomerID
IN (SELECT CustomerID
FROM Customers
WHERE CustLastName = 'Bonnickson') AND N.CustomerID NOT IN (SELECT CustomerID
FROM Customers
WHERE CustLastName = 'Rosales')

but alas, it returned no rows. thanks for the help. i'm going to keep playing with it and see where it goes.

[bbolte]

still returns 7 rows, what am i doing wrong?

SELECT Entertainers.EntertainerID, Entertainers.EntStageName
FROM Entertainers
WHERE EXISTS
(SELECT Customers.CustomerID
FROM Customers
INNER JOIN Engagements
ON Customers.CustomerID = Engagements.CustomerID
WHERE Customers.CustLastName = 'Bonnicksen'
AND Engagements.EntertainerID = Entertainers.EntertainerID)
AND Entertainers.EntertainerID NOT IN
(SELECT Customers.CustomerID
FROM Customers
INNER JOIN Engagements
ON Customers.CustomerID = Engagements.CustomerID
WHERE Customers.CustLastName = 'Rosales'
AND Engagements.EntertainerID = Entertainers.EntertainerID)

[DaveMaxwell]

OK, you had me curious enough that I had to try this out and see what was going on. It was the not in query that was causing problems for some reason.

This query WILL work (at least it does for my data):

SELECT DISTINCT EntertainerID, EntStageName
  FROM Entertainers
WHERE  EntertainerID in (SELECT EntertainerID FROM Engagements where customerID in (select customerID from Customers where CustomerName = 'Bonnickson'))
     AND EntertainerID NOT IN (SELECT EntertainerID FROM Engagements where customerID in (select customerID from Customers where CustomerName = 'Rosales'))

[bbolte]

it's blowing my mind. i get nothing...

[DaveMaxwell]

Here is what I created:

Table: Customer (CustomerID, CustomerName)
------------------------------------------
1      Rosales
2      Bonnickson
3      Maxwell

Table: Entertainer (EntertainerID, EntStageName)
------------------------------------------------
1      Bob
2      Ralph 
3      George
4      Alice
5      Jane
6      Sue
7      Tom
8      John 

Table:  Engagements (EngagementID, CustomerID, EntertainerID)
----------------------------------------------
1      1     1
2      1     2
3      1     3
4      1     6
5      2     2
6      2     4
7      2     6
8      2     8
9      3     1
10     3     3
11     3     5
12     3     7

Now according to your scenario, I should see Alice and John (Bonnickson has seen Ralph(2), Alice(4), Sue(6) and John(8), but Rosales has seen Ralph(2) and Sue(6)). That's the result I get from that last post I made. Am I missing something?

[bbolte]

dave i truly appreciate all your help. i don't know what else to try. played with it some more and it didn't work. i may have to just take the miss on this one.

thanks again!

[bbolte]

Dave, here is what i got to work, what do you think?

SELECT b.EntStageName
FROM
(SELECT DISTINCT Entertainers.EntStageName
FROM (Entertainers
INNER JOIN Engagements
ON Engagements.EntertainerID = Entertainers.EntertainerID)
INNER JOIN Customers
ON Customers.CustomerID = Engagements.CustomerID
WHERE Customers.CustLastName LIKE 'bon*') AS b
WHERE b.EntStageName NOT IN
(SELECT DISTINCT Entertainers.EntStageName
FROM (Entertainers
INNER JOIN Engagements
ON Engagements.EntertainerID = Entertainers.EntertainerID)
INNER JOIN Customers
ON Customers.CustomerID = Engagements.CustomerID
WHERE Customers.CustLastName LIKE 'ros*')

[DaveMaxwell]

It works. Seems a lot harder than the way I did it, but it works for me....

[bbolte]

yeah, it does. not sure why i couldn't get your query to run either - just the way it is i suppose. thanks again for your help!

[shane]

Using Dave's queries I get all 4 of the Entertainers that have had engagements with 'Bonnickson'. This is what you'd expect as each row in the resultset is evaluated. So you're going to get rows for Sue and Ralph where the CustomerID is 2, which fulfills the WHERE clause. (ie 2 is in 2 and is not in 1 - if you see what I mean.)

Dave, I'm kinda puzzled as to how you just got Alice and John from the queries.

Resultset obtained by dropping the where clause and adding the CustomerID to the SELECT clause.

EntStageName	CustomerID  
--------------- ----------- 
Alice		2           
Bob		3           
Bob		1           
George		3           
George		1           
Jane		3           
John		2           
Ralph		2           
Ralph		1           
Sue		2           
Sue		1           
Tom		3

I think what you need to do is get the count for each Entertainer, for each criteria. There's probably a cleaner way of doing this other than using correlated subqueries but the query below works for me.

(I'm using the same data as Dave posted)

SELECT	EntStageName
FROM	Entertainers E
WHERE	(SELECT COUNT(*)
		FROM Engagements ENG1
		INNER JOIN Customers C1 ON ENG1.CustomerID = C1.CustomerID
		WHERE ENG1.EntertainerID = E.EntertainerID 
		AND C1.CustomerName = 'Bonnickson') > 0
AND	(SELECT COUNT(*)
		FROM Engagements ENG2
		INNER JOIN Customers C2 ON ENG2.CustomerID = C2.CustomerID
		WHERE ENG2.EntertainerID = E.EntertainerID 
		AND C2.CustomerName = 'Rosales') = 0

[bbolte]

hey, thanks as well!

[DaveMaxwell]

Originally posted by shane

Dave, I'm kinda puzzled as to how you just got Alice and John from the queries.

Shane....

The first query I posted was definitely bogus as I got the same results you did. That's why I changed the query to this:

SELECT DISTINCT EntertainerID, EntStageName
  FROM Entertainers
WHERE  EntertainerID in (SELECT EntertainerID FROM Engagements where customerID in (select customerID from Customers where CustomerName = 'Bonnickson'))
     AND EntertainerID NOT IN (SELECT EntertainerID FROM Engagements where customerID in (select customerID from Customers where CustomerName = 'Rosales'))

What this query does is get ALL entertainers from the engagements table that had Bonnickson as a customer. It then checks for ALL entertainers from the engagements table that had Rosales as a customer. It then compares the two lists and only returns the one's that were on the Bonnickson list and NOT on the Rosales list....

That make more sense?

[shane]

I gotta admit I didn't actually try your 3rd query. But reading it, it does make sense.

The row count method seems more readable to me though. It'd be interesting to see which one would perform better.