SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Wizard bbolte's Avatar
    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 here
    Last edited by bbolte; Feb 11, 2002 at 22:57.

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 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')
    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:
    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

  3. #3
    SitePoint Wizard bbolte's Avatar
    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.

  4. #4
    SitePoint Wizard bbolte's Avatar
    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.

  5. #5
    SitePoint Wizard bbolte's Avatar
    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.

  6. #6
    SitePoint Wizard bbolte's Avatar
    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)

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 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

  8. #8
    SitePoint Wizard bbolte's Avatar
    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...

  9. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 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
    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?
    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

  10. #10
    SitePoint Wizard bbolte's Avatar
    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!

  11. #11
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile 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*')

  12. #12
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 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

  13. #13
    SitePoint Wizard bbolte's Avatar
    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!

  14. #14
    SitePoint Wizard
    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 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)

    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

  15. #15
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey, thanks as well!

  16. #16
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 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.
    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:

    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'))
    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?
    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

  17. #17
    SitePoint Wizard
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •