CODEWITHSUNDEEP
mysqlsql
Helmi
Helmi

Reputation: 539

Select users from table that have at least one entry in diffierent table

I'm looking for a way to select users from a user table that have at least one relating entry in another table. Probably my join approach is totally wrong but that is what I was trying and should give you an idea of the structure:

SELECT users.`id`, users.`email`, users.`username`, users.`status`, users.`date_reg`
FROM dir_editors as users
JOIN dir_links as links ON (links.`id_editor` = users.`id`)
WHERE COUNT(links.`id_editor`) > 1

So the goal is to get all these user data from user that have at least one link entry in the dir_links table where the id_editor is the field in the dir_links table.

Thanks for helping

Upvotes: 3

Views: 1690

Answers (3)

Mike
Mike

Reputation: 2005

SELECT users.`id`, users.`email`, users.`username`, users.`status`, users.`date_reg`
 FROM dir_editors as users
WHERE EXISTS(SELECT 1 FROM dir_links as links WHERE links.`id_editor` = users.`id`)

Upvotes: 5

kjmerf
kjmerf

Reputation: 4335

An INNER JOIN selects the records that are found in both tables, depending on a key that you put in your ON statement:

SELECT users.id, users.email, users.username, users.status, users.date_reg
FROM dir_editors AS users
INNER JOIN dir_links as links
ON links.id_editor = users.id

Upvotes: 0

Hamza Zafeer
Hamza Zafeer

Reputation: 2436

SELECT users.`id`, users.`email`, users.`username`, users.`status`, users.`date_reg`
FROM dir_editors as users
JOIN dir_links as links ON (links.`id_editor` = users.`id`)

Remove this WHERE COUNT(links.id_editor) > 1 because this imposing condition.

INNER JOIN: Returns all rows when there is at least one match in BOTH tables

Upvotes: 0

Related Questions