Reply to Re: mysql help needed

Your name:

Reply:


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]


УдалСнная Ρ€Π°Π±ΠΎΡ‚Π° для программистов  •  Как Π·Π°Ρ€Π°Π±ΠΎΡ‚Π°Ρ‚ΡŒ Π½Π° Google AdSense  •  England, UK  •  ΡΡ‚Π°Ρ‚ΡŒΠΈ Π½Π° английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Π‘Π°ΠΉΡ‚ ΠΈΠ·Π³ΠΎΡ‚ΠΎΠ²Π»Π΅Π½ Π² Π‘Ρ‚ΡƒΠ΄ΠΈΠΈ Π’Π°Π»Π΅Π½Ρ‚ΠΈΠ½Π° ΠŸΠ΅Ρ‚Ρ€ΡƒΡ‡Π΅ΠΊΠ°
ΠΈΠ·Π³ΠΎΡ‚ΠΎΠ²Π»Π΅Π½ΠΈΠ΅ ΠΈ ΠΏΠΎΠ΄Π΄Π΅Ρ€ΠΆΠΊΠ° Π²Π΅Π±-сайтов, Ρ€Π°Π·Ρ€Π°Π±ΠΎΡ‚ΠΊΠ° ΠΏΡ€ΠΎΠ³Ρ€Π°ΠΌΠΌΠ½ΠΎΠ³ΠΎ обСспСчСния, поисковая оптимизация