sql errore sintassi

[jimbo0]

sto uscendo di testa cercando di capire dov'è l'errore di sintassi (riga 7: ON p.userid = c.user_id )

SELECT pts.userid, pts.points, com.value

  FROM (SELECT c.value, MAX(p.points) AS max_pts
        FROM  k6l0c_community_fields_values AS c
	WHERE c.value=16
         INNER JOIN  k6l0c_alpha_userpoints AS p             
         	ON p.userid = c.user_id   
	INNER JOIN  k6l0c_user_usergroup_map AS grp
		ON c.user_id = grp.user_id	
				WHERE   grp.group_id = 2
         GROUP BY c.value ) AS sub
INNER
  JOIN  k6l0c_community_fields_values AS com  
    ON com.value = sub.value     
INNER
  JOIN  k6l0c_alpha_userpoints AS pts
    ON pts.points = sub.max_pts
   AND pts.userid = com.id
INNER
  JOIN  k6l0c_users AS usr
    ON usr.id = pts.userid  


WHERE com.value!='' AND
       pts.points >0
	
ORDER BY pts.points DESC

[123delphi321]

 1   SELECT 
 2   pts.userid, 
 3   pts.points, 
 4   com.value
 5   FROM (
 6          SELECT 
 7          c.value, 
 8          MAX(p.points) AS max_pts
 9          FROM  k6l0c_community_fields_values AS c
10          WHERE c.value=16
11          INNER JOIN  k6l0c_alpha_userpoints AS p     ON p.userid = c.user_id   
12          INNER JOIN  k6l0c_user_usergroup_map AS grp ON c.user_id = grp.user_id  
13          WHERE grp.group_id = 2
14          GROUP BY c.value ) AS sub
15   INNER JOIN  k6l0c_community_fields_values AS com ON com.value = sub.value     
16   INNER JOIN  k6l0c_alpha_userpoints AS pts        ON pts.points = sub.max_pts AND pts.userid = com.id
17   INNER JOIN  k6l0c_users AS usr                   ON usr.id = pts.userid  
18   WHERE com.value!='' AND pts.points >0
19   ORDER BY pts.points DESC

ciao,

non vorrei sbagliare ma a me sembra che ci sono 2 where al rigo 10 e al rigo 13.
credo sia quello l'errore, il where deve andare dopo INNER JOIN che sta al rigo 12

facci sapere.
ciao

[jimbo0]

mi sa che hai ragione, ho aggiunto la condizione alla riga 10 come AND del where alla riga 13.
Ora va, ma ora sono passato a qualche errore logico, perché mi restituisce 0 risultati.

la struttura delle tabelle è questa

alpha_userpoints
________________
userid | points
880      15
850      20


community_fields_values
_________________________
user_id | field_id | value
880        2         roma
880        5         16 anni
850        2         firenze

user_usergroup_map serve solo ad escludere tutti gli utenti che non hanno group_id=2.

Lo scopo della query: selezionare l'userid del punteggio più alto (alpha_userpoints) per ogni città (fields_id=2). In altre parole mi serve 1 solo userid per ogni città: quello che ha il punteggio più alto.

any help?

query allo stato attuale

SELECT pts.userid, pts.points, com.value

  FROM (SELECT c.value, MAX(p.points) AS max_pts
        FROM  k6l0c_community_fields_values AS c
   
         INNER JOIN  k6l0c_alpha_userpoints AS p             
            ON p.userid = c.user_id   
    INNER JOIN  k6l0c_user_usergroup_map AS grp
        ON c.user_id = grp.user_id  
                WHERE   grp.group_id = 2 AND c.field_id=16
         GROUP BY c.field_id ) AS sub
INNER
  JOIN  k6l0c_community_fields_values AS com  
    ON com.value = sub.value     
INNER
  JOIN  k6l0c_alpha_userpoints AS pts
    ON pts.points = sub.max_pts
   AND pts.userid = com.id
INNER
  JOIN  k6l0c_users AS usr
    ON usr.id = pts.userid  




WHERE com.value!='' AND
       pts.points >0
    
ORDER BY pts.points DESC

[123delphi321]

penso l'errore sia banale....

la subquery restituisce record?

verifica se la sub-query restituisce record....cosi scoprirai dove hai inserito la condiziona che non fa filtrare record