I've got 3 tables: members, photos and marks. Members can post pictures and rank the pictures of the other members.
I'm doing a script in PHP which returns a photo (using random()) that can be ranked by the user connected in his member area. Thus, I made the following query (I commented the line which obviously contain a problem):
SELECT ph.*,
COUNT(note) 'nb_note',
ROUND(DEGREES(ACOS((SIN(RADIANS( 48.86 )) * SIN (RADIANS( v.latitude_deg ))) + (COS(RADIANS( 48.86 )) * COS(RADIANS( v.latitude_deg )) * COS( RADIANS( 2.34445 - v.longitude_deg))))) * 111.13384) 'distance',
((UNIX_TIMESTAMP() - UNIX_TIMESTAMP(mb.anniv)) / 3600 / 24 / 365) AS 'age' , mb.sexe, pr.orientation, mb.pseudo, mb.anniv,mb.ID 'ID_membre'
FROM photos__ ph
LEFT JOIN photos__rank rk ON rk.ID_photo = ph.ID
LEFT JOIN photos__signalements sg ON sg.ID_photo = ph.ID
INNER JOIN membre__ mb ON mb.ID = ph.ID_membre
INNER JOIN membre__profil pr ON pr.ID_membre = mb.ID
INNER JOIN site__villes v ON v.ID = pr.ID_ville
// '96' is the currently connected member, written into the query by PHP
WHERE mb.ID <> '96'
// THE FOLLOWING LINE SHOULD PREVENT THE QUERY TO RETURN PICTURES ALREADY RANKED // BY THE USER CURRENTLY CONNECTED, BUT IT DOESN'T WORK :
AND (rk.ID_membre <> '96' OR ISNULL(note)) // THIS LINE DOESN'T WORK
// SAME PROBLEMS WITH PICTURES ALREADY REPORTED BY THE USER '96' (the connected):
AND (sg.ID_membre <> '96' OR ISNULL(sg.ID)) // AND THIS ONE AS WELL
AND ph.innotable = 0
AND mb.sexe = 'f'
AND pr.orientation IN ('hetero', 'bi')
GROUP BY ph.ID HAVING distance < 10 AND age >= 16
ORDER BY RAND() LIMIT 1
well, I wrote a "group by" clause to return just the row with the informations I need to display (distance, age, ID of the member, ID of the photo etc) . The problem is, when several members has already ranked the same photo, this query can return photos already ranked by the user. I found it's because when I say "where rk.ID_membre <> '96' OR ISNULL(note)" i say to mysql "you can return a photo if it has no marks yet or if the first mark you find is different from '96'". How to say "you can return if it has no marks or if it has, all the marks must be different from '96').
I need a SQL function as COUNT or AVG which return if an int is or not in the agregated column. I would do sth like
SELECT .. all the other infos ..,
IS_THERE('96' IN photo) AS 'already_ranked',
IS_THERE('96' IN signalements) 'already_reported'
..blablabla...
WHERE/HAVING already_reported = 0 AND already_ranked = 0
GROUP BY photos.ID
Don't hesitate to tell me if there is another way faster or more simple to make this query.
