|
Posted by Bob Bedford on 07/06/06 10:05
"Paul Lautman" <paul.lautman@btinternet.com> a ιcrit dans le message de
news: 4h425mF1prj87U1@individual.net...
> 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
Hi Paul,
exactly !!! thanks !
Why it does seem so simple once the answer is known ??? Didn't think to put
the distinct inside the ()...
Great, thanks again for your help.
[Back to original message]
|