|
|
Posted by Paul Lautman on 07/06/06 09:07
Bob Bedford wrote:
> Hi all,
>
> I've ever the same problem.
>
> Table1
> idperson, name, zip
>
> table2
> zip, city, region.
>
> Table2: same zip, many cities (1000, Lausanne - 1000, Lausanne1, ...)
>
> Now I'd like ONE query wich return the name, zip and city, but just
> one record per zip.
>
> I mean if a record in table1 has zip 1000, I only want the first
> occurence of the city (in my case Lausanne).
> Actually I get as many records as they are cities with the same zip.
>
> namex,1000,Lausanne
> namex,1000,Lausanne1
> namex,1000,LausanneN....
>
> How to do so ? in fact what I need is count the number of peoples in a
> region, and the region is linked to a zip.
>
> 1000, Lausanne, VD
> 1000, Lausanne1, VD....
>
> the actual query is
> select count(idperson), region from table1 inner join table2 on
> table1.zip = table2.zip group by region.
> Please help.
>
> Bob
Would you be looking for this:
SELECT count( DISTINCT a.idperson ) , b.region
FROM `table1` a
JOIN `table2` b
USING ( zip )
GROUP BY region
Navigation:
[Reply to this message]
|