Select Same or Different Names from Zip Code

[Frank_Rizzo]

How can I select 2 or more names (either the same or different) from a large table split by zip code.

e.g.

Zipcode, Name

55512, A Smith
55512, A Smith
55512, B Jones
55512, M Davies
12022, A Smith
12022, Y Beck
12022, Z Zak
12022, D Johnson
24680, A Smith
24680, B Jones
24680, Y Beck
24680, Z Zak

In summary, I need to extract data where there are two or more of a single name per zip, and where there are two or more of two names per zip.

1. Select zipcode and names where there are 2 or more A Smith per zipcode

search for single name: A Smith

should return

55112, A Smith
55112, A Smith

2. Select zipcode and names where there are zipcodes with both A Smith and B Jones per zipcode

search for name 1: A Smith search for name 2: B Jones

should return

55512, A Smith
55512, A Smith
55512, B Jones
24680, A Smith
24680, B Jones

TIA.

[Frank_Rizzo]

The only way I can do this for 1. (single name) is to

Select the name ordered by zip
Loop through each row returned
Count the number of rows in the same zip block
If more than 2 then store in array

That's easy enough but 2 is a bit more complicated. Can be done with php but just wondered if there was a select which would make either 1. or 2. easier.

[edited by: Frank_Rizzo at 9:05 am (utc) on Sep. 4, 2007]

[syber]

Would this give you what you want for #1?

SELECT zipcode, name  
FROM table 
WHERE zipcode+name IN (SELECT zipcode+name  
 FROM table  
 GROUP BY zipcode+name  
 HAVING COUNT(*) > 1) 

[Frank_Rizzo]

I gave that example a try but stopped it after 60 seconds. I have an index on zipcode and name. Here's the explain

mysql> explain SELECT zipcode, name FROM widgets WHERE zipcode+name IN (SELECT zipcode+name FROM widgets GROUP BY zipcode,name HAVING COUNT(*) > 1);

+----+--------------------+----------+------+---------------+------+---------+------+--------+---------------------------------+
¦ id ¦ select_type ¦ table ¦ type ¦ possible_keys ¦ key ¦ key_len ¦ ref ¦ rows ¦ Extra ¦
+----+--------------------+----------+------+---------------+------+---------+------+--------+---------------------------------+
¦ 1 ¦ PRIMARY ¦ widgets ¦ ALL ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦ 140356 ¦ Using where ¦
¦ 2 ¦ DEPENDENT SUBQUERY ¦ widgets ¦ ALL ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦ 140356 ¦ Using temporary; Using filesort ¦
+----+--------------------+----------+------+---------------+------+---------+------+--------+---------------------------------+
2 rows in set (0.01 sec)

[aspdaddy]

I dont know the syntax for mysql but in t-sql this works: (you could also do the view as a subquery maybe)

Create View dbo.vwSummary as 
select [Name],Zip, Count(name) as [Count] from tblNames
GROUP BY [Name],Zip
GO
SELECT [Name],Zip FROM dbo.vwSummary
WHERE [Count]>1 AND Name IN ('A Smith', 'B Jones')

HTH

[AussieWebmaster]

hopefully you are not using different case (upper in one lower in another) because the first sql statement looks right

[syber]

GROUP BY zipcode+name - NOT GROUP BY zipcode,name