SQL help.....

Anon

I have a script I'm working on at the moment which needs to make a call to my DB (MySQL) structure as follows:

#############################################################################
#

TABLE 'bikes'

id,style_id,mf,range_id,model_name,detail,gears,finish,sizes,trade_price,model_img,model_tn_img,type_img

TABLE 'range'

id,range,range_full,logo_img

TABLE 'style'

id,style

#
#############################################################################

Now what I want to do is for the second call of the script (first call selects the "range" of bikes) is for it to display a list of the styles available that are available within that range. Format of script call resulting from what I want will be 'bikes.php?r=1&s=1' etc where r is the range_id and s is the style_id, and the call to the script to get what I want will be 'bikes.php?r=1' etc.

So my SQL needs to select a list of styles available for a given range. I came up with the following SQL:

SELECT style.style,style.id FROM bikes LEFT JOIN style ON bikes.style_id=style.id GROUP BY style.id ,style.style,bikes.range_id HAVING bikes.range_id = 1;

and yet phpMyadmin returns the following;
MySQL said: Unknown column 'bikes.range_id' in 'having clause'

What I don't understand is that bikes.range_id exists! Any suggestions as to where I went wrong????

tomhath

I can't tell what you are trying to do with the HAVING clause, did you mean to use a WHERE? The cloumn you use in HAVING must be one that you SELECT (in this case you could only use style.style or style.id)

Try this:

SELECT style.style,style.id FROM bikes LEFT JOIN style ON bikes.style_id=style.id
where bikes.range_id = 1
GROUP BY style.id ,style.style,bikes.range_id

Anon

tomhath wrote:

Try this:

SELECT style.style,style.id FROM bikes LEFT JOIN style ON bikes.style_id=style.id
where bikes.range_id = 1
GROUP BY style.id ,style.style,bikes.range_id

Perfect, thanks very much, it works a treat!

The original worked in Access but failed to operate as desired in MySQL