Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Posts
    2

    Red face Unanswered: Query (dont know how to title this)

    Here is the table

    Code:
    Place     |Name
    -----------------------
    Denver    |Jane
    Denver    |Marianne
    Denver    |Morgan
    Boston    |Emma
    Boston    |Phill
    Boston    |Marianne
    Atlanta   |Morgan
    Atlanta   |Mark
    Atlanta   |Marianne
    I want to get everything where Marianne and Morgan both were (Denver and Atlanta). How?!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Try this:
    Code:
    SELECT p1.place
    FROM places p1 JOIN (SELECT place
                         FROM places
                         WHERE name = 'Morgan') p2 ON (p1.place = p2.place)
    WHERE p1.name = 'Marianne';

  3. #3
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    I believe the following should work as well -
    Code:
    SELECT place, count(*) as cnt FROM your_table WHERE name = 'marianne' OR name = 'morgan' GROUP BY place HAVING cnt = 2

  4. #4
    Join Date
    Jan 2007
    Posts
    2

    Thank you

    Thank you both!

    However, shammat`s solution was significantly faster!

    It took 0.0314s vs 0.5130s (dbmab`s solution) on 250k records tables.

    Thank you, again.

  5. #5
    Join Date
    Dec 2006
    Location
    Bristol, England
    Posts
    7

    interesting...

    Wow... I find that quite suprising. Interesting

    What were you doing to measure the performance?

  6. #6
    Join Date
    Dec 2006
    Location
    Bristol, England
    Posts
    7
    Just looked a bit closer at the dbmab solution and it's not actually correct as it's only checking they were both in two places not necessarily the same places and it's not constrained to Denver and Atlanta.

    I think this would work:

    SELECT Name
    FROM YourTable
    WHERE (Name = 'marianne' OR Name = 'morgan') And (Place = 'Denver' Or Place = 'Atlanta')
    GROUP BY Name
    HAVING COUNT(Name) = 2

    This could perform slightly better if the name field is indexed. Personally I find group by easier to understand than joinging a table to itself... but if the performance gap remains I'd go with the join

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tom, i don't agree with your solution

    the original question was to "get everything where Marianne and Morgan both were"

    you wouldn't know in advance that it was only Denver and Atlanta -- maybe they both were in Kalamazoo too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2006
    Location
    Bristol, England
    Posts
    7
    Sorry in that case I misinterpreted the question as it says (Denver and Atlanta).

    It's fun doing SQL without the database - gets the brain ticking

  9. #9
    Join Date
    Mar 2004
    Posts
    480
    dbmab's solution scales better:
    Code:
    SELECT place, count(*) as cnt 
    FROM your_table 
    WHERE name = 'marianne' OR name = 'morgan' 
    GROUP BY place 
    HAVING cnt = 2
    note that the names can be an IN list instead, making it easier to read with more names:

    Code:
    SELECT place, count(*) as cnt 
    FROM your_table 
    WHERE name IN ('marianne','morgan')
    GROUP BY place 
    HAVING cnt = 2
    so if you want to find which cities have been visited by four friends, you expand the name list and change the count:

    Code:
    SELECT place, count(*) as cnt 
    FROM your_table 
    WHERE name IN ('larry','curly','moe','shemp')
    GROUP BY place 
    HAVING cnt = 4

Posting Permissions

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