Problema query ( l'ennesimo -.- )

[zacca94]

Okkei, credo di avercela fatta a capire il funzionamento di tutto xD, però questa query non riesco a crearla...

Query:

$q = $database->query("SELECT u.*, g.*, o.* FROM users AS u INNER JOIN groups AS g ON g.group_id = u.groups LEFT JOIN online AS o ON o.user_id = u.user_id WHERE u.user_id = ".(int)$user_data[0]." AND u.user_password = '".$database->escape($user_data[1])."'") or die(mysql_errno() . ':' . mysql_error()); 

Questa query funziona, solo che devo pure estrarre i messaggi privati NON letti...

la tabella dei pm è:

-- --------------------------------------------------------
-- Crea tabella per i messaggi privati
-- --------------------------------------------------------
DROP TABLE IF EXISTS `mp`;

CREATE TABLE `mp`(
    `mid` int unsigned NOT NULL auto_increment,
    `name` varchar(100) NOT NULL default '',
    `text` text,
    `read` tinyint unsigned NOT NULL default '0',
    `sender` varchar(25) NOT NULL default '',
    `sender_id` smallint unsigned NOT NULL default '0',
    `receiver` varchar(25) NOT NULL default '',
    `receiver_id` smallint unsigned NOT NULL default '0',
PRIMARY KEY(`mid`)
)TYPE=MyISAM DEFAULT CHARSET=utf8; 

Ho provato a fare una Left Join per estrarre tutti i i messaggi NON letti in questo modo ( però non funziona ):

SELECT u.*, g.*, o.*, COUNT(read) as num_mp
FROM users AS u 
INNER JOIN groups AS g ON g.group_id = u.groups 
LEFT JOIN online AS o ON o.user_id = u.user_id 
LEFT JOIN mp AS pm ON receiver_id = u.user_id
WHERE read = 0 AND
u.user_id = "{id_utente_cookie}" AND
u.user_password = '"{pass_utente_cookie}"'") 

suggerimenti ???

[zacca94]

ho risolto da solo ( meglio così ), se a qualcuno può essere d'aiuto posto la query:

SELECT u.*, g.*, o.*, COUNT(pm.read) as num_mp
FROM users AS u
INNER JOIN groups AS g ON g.group_id = u.groups
LEFT JOIN online AS o ON o.user_id = u.user_id
LEFT JOIN mp AS pm ON pm.receiver_id = u.user_id
WHERE pm.read = 0 AND
u.user_id = "{id_utente_cookie}" AND
u.user_password = '"{pass_utente_cookie}"'")