AntoineLev AntoineLev - 1 month ago 20
SQL Question

Complex sql select

I can't figure out how to make this sql select statement...Here are my tables :

enter image description here

I opened the tables concerned by the request

So basically

I want to select the number of albums for each interpret.

I just can't figure it out... I am currently thinking that I need to do my first select on album like :

select interpret.no_interpret, count(*)
from album
.
.
.
group by interpret.no_interpret;


and there work from this but I don't know where to go next.

Answer

I may be missing something, but I'm not seeing the direct relation from your song table to the album...

I would first start by getting the link_interpret_song table joined to the song table and get count of distinct albums. However, I didn't see what appears to be a "No_Album" column in the field list of the song table. I can only guess it IS in there associated to the particular album. I did see media, but to me, that would be like a TYPE of media (digital, download, vinyl, CD) vs the actual ID Key apparent to the album table.

That said, I am thinking there IS such a "No_Album" column in the SONG table.

select
      LIS.No_Interpret,
      COUNT( DISTINCT S.No_Album )
   from
      Link_Interpret_Song LIS
         JOIN Song S
            on LIS.No_Song = S.No_Song
   group by
      LIS.No_Interpret;

Now, that said, if you want the interpret details, take the above results and join that to the interpret table. I've done both distinct album count and total # of songs just as an example of count() vs count(distinct) context... such as

select
      PreCounts.No_Interpret,
      PreCounts.DistinctAlbums,
      PreCounts.ActualSongs,
      I.Name_Interpret,
      I.First_Name,
      I.Stage_Name
   from
      ( select
              LIS.No_Interpret,
              COUNT( DISTINCT S.No_Album ) as DistinctAlbums,
              COUNT(*) as ActualSongs
           from
              Link_Interpret_Song LIS
                 JOIN Song S
                    on LIS.No_Song = S.No_Song
           group by
              LIS.No_Interpret ) as PreCounts
      JOIN Interpret I
         ON PreCounts.No_Interpret = I.No_Interpret