CODEWITHSUNDEEP
mysqlsqljoin
Lanbo
Lanbo

Reputation: 15692

Select all rows which have other elements associated via join

I have a table of users and comments:

users(id, name)
comments(id, user_id, content)

I am looking for an (efficient) SELECT statement which will return only those users-rows which have at least one comment associated to them.

Usually I can do SQL easily but at the moment I can't get my head around it.

Upvotes: 1

Views: 66

Answers (3)

Cris
Cris

Reputation: 13351

SELECT  u.*
FROM    users u
where u.id in (select id from comments where id=u.id)

Upvotes: 1

Skatox
Skatox

Reputation: 4284

It's as simple as 

SELECT DISTINCT id, name 
FROM users u
INNER JOIN comments c ON u.id = c.user_ID

INNER JOIN finds all the rows in users who whas linked data in comments

Upvotes: 2

John Woo
John Woo

Reputation: 263733

INNER JOIN will do the the job. The reason why DISTINCT was added on the select statement is because it will only return unique user records and there maybe users have more than one record on the comments table.

SELECT  DISTINCT a.*
FROM    users a
        INNER JOIN comments b
            ON a.id = b.user_ID

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 1

Related Questions