CODEWITHSUNDEEP
mysqlsubquery
user1037355
user1037355

Reputation:

SELECT all when ID is not in another table in one query

Typically with sub queries I usually get additional information on something from other tables. EG getting user details from one table, then in the same query do a sub query and get the users permissions from another. But this time i have found myself stumped, i need to get all the users from table 'primary' which do not exist (with a correlating job_ID) from another table, 'secondary'.

Tables:

primary
ID   |   email   |   name
1      [email protected]        a
2      [email protected]        b
3      [email protected]        c


secondary
ID   |  job_ID
1        1234

Query

SELECT
        ID,
        email
    FROM
        primary
    WHERE
        ID does not exist from this query (SELECT
                                                ID
                                            FROM
                                                secondary
                                            WHERE
                                                ID = `1` AND
                                                job_ID = `1234`)

The query should in theory return users with ID 1 and 2.

This would be easy to do with one query then looping the results and running other queries, but is this possible with one mysql query? From other posts on here it seems this is possible when the second match is only on one column and not on multiples like this one.

Upvotes: 1

Views: 2238

Answers (1)

Steve
Steve

Reputation: 3703

Yes, you can use IN for example:

SELECT id, email FROM primary
WHERE id NOT IN (SELECT id FROM secondary WHERE id = 1 AND job_id = 1234)

That still uses a sub query though, if you wanted to do it without a sub query then using JOIN would achieve the same result. For example, to show all information when it doesn't exist in the other table:

SELECT p.id, p.email FROM primary_table p
LEFT JOIN secondary_table s ON s.id = p.id
WHERE s.job_id IS NULL

To see this query in action, please visit this SQL Fiddle

Upvotes: 2

Related Questions