SQL multiple Joing Question, cant join 5 tables, problem with max

Question

I got 6 tables:

Albums

id_album | title | id_band | year |

Bands

id_band | name |style | origin 

composers

id_musician | id_song 

members

id_musician | id_band | instrument

musicians

id_musician | name | birth | death | gender

songs

id_song | title | duration | id_album 

I need to write a query where I get the six bands with more members and of those bands, get the longest song duration and it's title.

So far, I can get the biggest bands:

SELECT bands.name, COUNT(id_musician) AS numberMusician
FROM bands
INNER JOIN members USING (id_band)
GROUP BY bands.name
ORDER BY numberMusician DESC
LIMIT 6;

I can also get the longest songs:

SELECT MAX(duration), songs.title, id_album, id_band
FROM SONGs
INNER JOIN albums USING (id_album)
GROUP BY songs.title, id_album, id_band
ORDER BY MAX(duration) DESC

The problem occurs when I am trying to write a subquery to get the band with the corresponding song and its duration. Trying to do it with inner joins also gets me undesired results. Could someone help me?

I have tried to put the subquery in the where, but I can't find how to do it due to MAX.

Thanks

Answer 1

I find that using lateral joins makre the query easier to write. You already have the join logic all right, so we just need to correlate the bands with the musicians the songs.

So:

select b.name, m.*, s.*
from bands b
cross join lateral (
    select count(*) as cnt_musicians
    from members m 
    where m.id_band = b.id_band
) m
cross join lateral (
    select s.title, s.duration
    from songs s
    inner join albums a using (id_album)
    where a.id_band = b.id_band
    order by s.duration desc limit 1
) s
order by m.cnt_musicians desc
limit 6

For each band, subquery m counts the number of musicians per group (its where clause correlates to the outer query), while s retrieves the longest song, using correlation, order by and limit. The outer query just combines the information, and then orders selects the top 6 bands.