complex query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
SirChick
Forum Contributor
Posts: 125
Joined: Tue Jul 31, 2007 11:55 am

complex query

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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 "
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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
Post Reply