CODEWITHSUNDEEP
phpmysqlsql
Belgin Fish
Belgin Fish

Reputation: 19837

Select items where ID present in another column MySQL

I have a table of albums, each album has an ID.

I also have a table of reviews, each review is linked to a corresponding album through the column albumID.

I'm wondering how I can select all albums who's ID's are present in the reviews table.

I was thinking of selecting all albumId's from the reviews column then doing a select where id in but I feel like this would be horribly inefficient.

This is in MySQL

Albums Table
- ID

Reviews Table
- ID
- albumID

Desired result: All albums who have a review. (eg: All albums that have their ID present in the Reviews table)

Upvotes: 0

Views: 433

Answers (2)

xQbert
xQbert

Reputation: 35323

Here's one approach using a simple JOIN. The inner join notation will only include records that exist in both tables. It allows you to access data in both tables which may be useful if you need data out of reviews too.

Select * from albums A
inner join reviews R
 on A.ID = R.AlbumID

This next approach is generally the fastest but you can only get data from albums. This uses what's known as a correlated sub query and is generally the fastest approach. 

SELECT * from albums A
where exists (Select 1 from reviews R where A.ID = R.albumID)

and a third approach but generally the slowest... uses a concept called IN(). The sub query here generates a list of ID's from reviews and only shows albums in that list.

Select * from albums where ID IN (SELECT albumID from Reviews)

Upvotes: 3

Asaph
Asaph

Reputation: 162771

This would work:

select a.* from albums as a
inner join reviews as r
on r.albumID = a.ID

Upvotes: 1

Related Questions