SitePoint Sponsor |
|
User Tag List
Results 1 to 17 of 17
Thread: sql statement help needed
-
Feb 11, 2002, 21:52 #1
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
sql statement help needed
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 hereLast edited by bbolte; Feb 11, 2002 at 22:57.
-
Feb 12, 2002, 07:30 #2
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
I think you just made it to hard on yourself. Try this:
Code: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')
Code:SELECT DISTINCT EntStageName FROM ENTERTAINERS E, ENGAGEMENTS N WHERE E.EntertainerID = N.EntertainerID AND N.CustomerID = 10011 AND N.CustomerID <> 10014
Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 12, 2002, 09:04 #3
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
thanks, i will give it a try after a bit. got to get some work done first.
-
Feb 12, 2002, 11:09 #4
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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...
Last edited by bbolte; Feb 12, 2002 at 11:14.
-
Feb 12, 2002, 11:14 #5
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Feb 12, 2002, 14:19 #6
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
tried another way...
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)
-
Feb 12, 2002, 15:02 #7
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
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):
Code: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'))
Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 12, 2002, 15:10 #8
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
it's blowing my mind. i get nothing...
-
Feb 12, 2002, 15:46 #9
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
Here is what I created:
Code: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
Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 12, 2002, 21:40 #10
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Feb 13, 2002, 10:59 #11
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
finally...
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*')
-
Feb 13, 2002, 11:17 #12
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
It works. Seems a lot harder than the way I did it, but it works for me....
Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 13, 2002, 14:05 #13
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Feb 13, 2002, 18:25 #14
- Join Date
- Jan 2001
- Location
- Milton Keynes, UK
- Posts
- 1,011
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Code: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'm using the same data as Dave posted)
Code: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
-
Feb 13, 2002, 19:39 #15
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
hey, thanks as well!
-
Feb 15, 2002, 08:20 #16
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
Originally posted by shane
Dave, I'm kinda puzzled as to how you just got Alice and John from the queries.
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:
Code: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'))
That make more sense?Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 15, 2002, 08:47 #17
- Join Date
- Jan 2001
- Location
- Milton Keynes, UK
- Posts
- 1,011
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Bookmarks