CODEWITHSUNDEEP
mysqlsql
dimaninc
dimaninc

Reputation: 850

how to get rows from table which are not mentioned in another table

i got 2 tables with following structure:

CREATE TABLE users(
  id bigint not null auto_increment,
  name varchar(255) default '',
  primary key(id)
);

CREATE TABLE user_links(
  user_id bigint,
  job_id bigint
);

so users are linked to jobs (jobs table structure is not needed for this question)

i need to get all users which are not linked to any job at one time using one query

for example, if i have tables with such data:

users:
id | name
1  | james
2  | fred

user_links:
user_id | job_id
1       | 5
1       | 6

then i need result rows like this:

id | name
2  | fred

Upvotes: 0

Views: 58

Answers (2)

G. Stoynev
G. Stoynev

Reputation: 7783

... assuming there are no NULL user_id in user_links:

SELECT *
FROM users
LEFT OUTER JOIN user_links ON users.id = user_links.user_id
WHERE user_links.user_id IS NULL

Upvotes: 3

zerkms
zerkms

Reputation: 254886

SELECT *
  FROM users
 WHERE id NOT IN (SELECT user_id
                    FROM user_links)

Upvotes: 0

Related Questions