PHP / MySQL - 令人困惑的查询

Question

Im trying to construct a query that goes over 3 tables and im COMPLETELY stumped ... my knowledge limit is basic 1 table query and i need some help before i stick my head in a blender.

I have the following query

SELECT * FROM internalrole WHERE introle = $imarole

Im fine with that part .. its the next thats getting me all stressed.

That query returns the following columns ( id, user_id, introle, proven, used )

What i then need to do is take the user_id from the results returned and use it to get the following

SELECT * FROM users WHERE id = user_id(from previous query) AND archive = 0 and status = 8

I need to put that into 1 query, but wait, theres more .... from the results there, i need to check if that user's 'id' is in the availability table, if it is, check the date ( column name is date ) and if it matches todays date, dont return that one user.

I need to put all that in one query :S ... i have NO IDEA how to do it, thinking about it makes my head shake ... If someone could help me out, i would be eternaly grateful.

Cheers,

Answer 1

SELECT u.*
    FROM internalrole ir 
        INNER JOIN users u
            ON ir.user_id = u.id
                AND u.archive = 0
                AND u.status = 8
        LEFT JOIN availability a
            ON ir.user_id = a.user_id
                AND a.date = CURDATE()
    WHERE ir.introle = $imarole
        AND a.user_id IS NULL /* User does NOT exist in availability table w/ today's date */

EDIT: This second query is based on the comments below, asking to show only users who do exist in the availability table.

SELECT u.*
    FROM internalrole ir 
        INNER JOIN users u
            ON ir.user_id = u.id
                AND u.archive = 0
                AND u.status = 8
        INNER JOIN availability a
            ON ir.user_id = a.user_id
    WHERE ir.introle = $imarole