sql question

ChicoMendez

i have 3 tables

a company table (jos_company)
a country table ( jos_country)
a link table (jos_compctry) so that we can place a company in several countries

what i want to do is only sow the countries that have companies in them

at the moment the following sql works but just gives me all countries with a total count for the companies in each country

SELECT c.id, c.country,c.code2,COUNT(cc.id) AS numoptions FROM jos_country AS c LEFT JOIN jos_compctry AS cc ON (cc.ctryid=c.id) WHERE c.cont=".$id." GROUP BY c.id ORDER BY c.country

however whenever i try to only bring back the countries with entries by adding 'AND numoptions>0 ' i get an error


anyone know how to do this ?
thanks

Delirium

Your code:

SELECT c.id, c.country,c.code2,COUNT(cc.id) AS numoptions FROM jos_country AS c LEFT JOIN jos_compctry AS cc ON (cc.ctryid=c.id) WHERE c.cont=".$id." GROUP BY c.id ORDER BY c.country

Try the following:
SELECT c.id, c.country,c.code2,COUNT(cc.id) AS numoptions FROM jos_country AS c LEFT JOIN jos_compctry AS cc ON (cc.ctryid=c.id) WHERE c.cont=".$id." GROUP BY c.id HAVING COUNT(cc.id)> 0 ORDER BY c.country

Hope this helps.

ChicoMendez

Thanks Alot!