CODEWITHSUNDEEP
phpmysql
Cecil
Cecil

Reputation: 1619

PHP / MySQL - Confusing Query

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,

Upvotes: 2

Views: 139

Answers (4)

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

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

Upvotes: 1

Marcus Frödin
Marcus Frödin

Reputation: 12892

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

Upvotes: 0

ryebr3ad
ryebr3ad

Reputation: 1248

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.

Upvotes: 5

liamgriffiths
liamgriffiths

Reputation: 348

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!

Upvotes: 0

Related Questions