Join 3 tables

Hi ALL!

I definitely need some help to join 3 mysql tables…
Here’s my ‘starting’ sql to join 2 tables - with this I select all the rows from the table ‘users’ where the members are in ‘contact’ (friends) with the logged member :


SELECT users.* 
FROM contacts
INNER JOIN users
ON users.id = contacts.receiver_id
WHERE contacts.asker_id = '$user_ID'
AND contacts.status = 1
UNION
SELECT users.*
FROM contacts
INNER JOIN users
ON users.id = contacts.asker_id
WHERE contacts.receiver_id = '$user_ID'
AND contacts.status = 1
ORDER BY id ASC

I’d like to expand this query as follows :
there’s the 3rd table ‘invitations’. I’d like to select ALL the rows from the table ‘users’ which are in ‘contact’ with the logged member (the query above is doing that) PLUS to see if a user have been already invited to that event. So if that member is already in the table ‘invitations’, I want to ignore it.
At the moment this is solved by executing a query inside the while loop, which is not a pretty good solution…


// while loop begins
$q_invited = mysql_query("SELECT id FROM invitations WHERE target_id = '$contact_ID'");
$invited_before = mysql_num_rows($q_invited);
if ($invited_before == 0) {
// echo the rows
}
// while loop ends

Hope I was clear enough…
Any help is appreciated!
:slight_smile:


SELECT u.*
FROM
  (SELECT users.* 
   FROM contacts
   INNER JOIN users
   ON users.id = contacts.receiver_id
   WHERE contacts.asker_id = '$user_ID'
   AND contacts.status = 1
   UNION
   SELECT users.*
   FROM contacts
   INNER JOIN users
   ON users.id = contacts.asker_id
   WHERE contacts.receiver_id = '$user_ID'
   AND contacts.status = 1
  ) AS u
LEFT OUTER JOIN invitations 
ON invitations.target_id = u.id
WHERE invitations.target_id IS NULL
ORDER BY u.id ASC

Thanks man! That’s pretty elegant!
:slight_smile: