PHP / MySQL - 令人困惑的查询

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,

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

Hmm, maybe something like this

SELECT * FROM users WHERE id IN (SELECT user_id FROM internalrole WHERE introle = $imarole) AND archive = 0 and status = 8;

A handy thing for me to remember is that tables are essentially arrays in SQL.

HTH!

Use INNER JOIN, which links tables to each other based on a common attribute (typically a primary - foreign key relationship)

say an attribute, 'id', links table1 and table2

SELECT t1.att1, t2.att2
FROM table1 t1
INNER JOIN table2 t2  
    ON t1.id = t2.id  --essentially, this links ids that are equal with each other together to make one large table row

To add more tables, just add more join clauses.

Nested queries are your friend.

SELECT * FROM users WHERE id in (SELECT user_id FROM internalrole WHERE introle = $imarole) AND archive = 0 and status = 8

Alternatively joins:

SELECT * FROM users INNER JOIN internalrole ON users.id = internalrole.user_id WHERE internalrole.user_id = $imarole AND users.archive = 0 and users.status = 8