CODEWITHSUNDEEP
mysqldatabase
Salil
Salil

Reputation: 47512

how to find albums using mysql query having 2 assets for each of its tracks

I want a mysql query for following scenario I have following database structure

1] table 'albums'  Columns  'id', 'name'
2] table 'tracks'  Columns  'id', 'title', 'album_id'   (album has many tracks)
3] table 'assets'  Columns  'id', 'track_id'   (track has many assets)

I want to find out all the albums such that, all the tracks of that album must have at least 2 assets.

For following test data albums

'id'    'name'
  1      Album 1
  2      Album 2

tracks

  'id'    'title'        'album_id'
   1      Track 1       1
   2      Track 2       1
   3      Track 3       2
   4      Track 4       2

assets

'id'  'track_id'
 1      1
 2      1
 3      2
 4      2
 5      3
 6      3
 7      4

So my query o/p should be 

'id'    'name'
  1      Album 1

As Album 1 has two tracks Track 1 and Track 2 and both of these tracks have 2 assets whereas Track 4 of Album 2 have only 1 asset

Upvotes: 0

Views: 145

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

Try:

SELECT    a.id,
          a.name
FROM      albums a
JOIN      tracks b ON a.id = b.album_id
LEFT JOIN (
          SELECT   track_id
          FROM     assets
          GROUP BY track_id
          HAVING   COUNT(*) >= 2
          ) c ON b.id = c.track_id
GROUP BY  a.id, 
          a.name
HAVING    COUNT(*) = COUNT(c.track_id)

SQLFiddle Demo

Upvotes: 4

Related Questions