Page 1 of 1

complex query

Posted: Wed Oct 17, 2007 5:35 pm
by SirChick
Ok let me just explain what this is meant to do ... its not working but i've not done such a complex query before.

Code: Select all

$CountNames = ("SELECT *, count(BusinessType) as C FROM `businesses` GROUP BY BusinessType HAVING C < 3");
$findbusinessresult = @mysql_query($CountNames) or die(mysql_error());
The idea of this is, the query is meant to find only the field "businesstype" where by the field is used only less than 3 times in the table... an example:

bacon
bacon
bacon
cheese
sausage
cheese
cheese


As you can see only sausage is used less than 3 times so sausage will show when I echo it... but at the moment it won't do it..any help is much appreciated.

Posted: Thu Oct 18, 2007 7:35 pm
by califdon
That won't work because at the time the query is selecting what to include, it hasn't yet seen all the rows, so it doesn't have any way to know what the count will be when it is finished. At least, I think that's the case. You need to write a compound query, probably something like:

Code: Select all

"SELECT *, C FROM (SELECT *, count(BusinessType) as C FROM `businesses`) WHERE C < 3 "

Posted: Fri Oct 19, 2007 12:04 am
by aaronhall
I see major db refactoring in your future. I'd be curious to see what your tables look like... there's got to be a better way