Query Help

[jaymc]

I am having trouble with the following query

 

SELECT

SQL_CALC_FOUND_ROWS

friends.user,

friends.friend,

friends.gender,

friends.x,

friends.timestamp,

gallery.timestamp as image

FROM friends

LEFT JOIN gallery ON friends.friend = gallery.user

 

WHERE friends.gender = 'Male' AND

friends.user = 'JMC' AND

gallery.valid = MAX(gallery.valid)

GROUP BY gallery.user

ORDER BY friends.x DESC, friends.timestamp DESC

LIMIT 0,5

 

I have highlighted in bold the part im having trouble with

 

 

 

This query does the following

 

1: selects data from the friends table where the users gender is Male, it then orders by a field called x

 

All that is fine

 

The problem Im having trouble with is the join, it queries the gallery table and pulls out the users image ID. Again thats working fine, however, the user will have multiple image ID's in the gallery, the one I want is the one where gallery.valid is the highest

 

2 is the highest, the others are 1,0,-1

 

In my example Ive used gallery.valid = MAX(gallery.valid) which obviously doesnt work

 

how can I join the image id onto my table but only join the image where gallery.valid is the highest

 

I can do WHERE gallery.valid = 2 as some people have images where gallery.valid = 1

 

It must only pull out the image ID for the user where valid is the highest

 

At the moment its pulling out the first image ID it comes to in the table

Hope you can help!

[fenway]

You can't use aggregate expressions in the where clause, they're not evaluted until after all the rows have been "seen" -- use having.