Query (dont know how to title this)

milos.sakovic · #1 (**permalink**) 01-22-07, 16:37

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?!

shammat · #2 (**permalink**) 01-22-07, 17:46

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';

dbmab · #3 (**permalink**) 01-22-07, 18:19

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

milos.sakovic · #4 (**permalink**) 01-22-07, 19:38

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.

tholder · #5 (**permalink**) 01-24-07, 05:35

Wow... I find that quite suprising. Interesting

What were you doing to measure the performance?

tholder · #6 (**permalink**) 01-24-07, 05:46

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

r937 · #7 (**permalink**) 01-24-07, 06:27

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

tholder · #8 (**permalink**) 01-24-07, 07:08

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

guelphdad · #9 (**permalink**) 01-24-07, 10:35

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

Internet Services

Web Development

Digital Marketing

Consumer Tech