Home > Blockchain >  SQL code for finding answer involving two tables
SQL code for finding answer involving two tables

Time:10-26

I have a database. These database has two tables.

One table is music.

name Date Edition Song_ID Singer_ID
LA 01.05.2009 1 1 1
Second 13.07.2009 1 2 2
Mexico 13.07.2009 1 3 1
Let's go 13.09.2009 1 4 3
Hello 18.09.2009 1 5 (4,5)
Don't give up 12.02.2010 2 6 (5,6)
ZIC ZAC 18.03.2010 2 7 7
Blablabla 14.04.2010 2 8 2
Oh la la 14.05.2011 3 9 4
Food First 14.05.2011 3 10 5
La Vie est.. 17.06.2011 3 11 8
Jajajajajaja 13.07.2011 3 12 9

And another table called singer

Singer nationality Singer_ID
JT Watson USA 1
Rafinha Brazil 2
Juan Casa Spain 3
Kidi USA 4
Dede USA 5
Briana USA 6
Jay Ado UK 7
Dani Australia 8
Mike Rich USA 9

Now I would like to know which Edition has most American Singer involved. But the problem is, there is only the Singer_ID.

Is there a SQL Code to find that?

I gave that code, but got an error

%sql SELECT Singer_ID, COUNT(*) FROM music GROUP BY Singer_ID WHERE nationality == USA from singer

CodePudding user response:

You need to use joins when information is present in 2 different tables. here the join would be in SINGER_ID

SELECT EDITION, COUNT(DISTINCT SINGER.SINGER_ID)
FROM MUSIC
INNER JOIN SINGER ON MUSIC.SINGER_ID = SINGER.SINGER_ID
WHERE SINGER.NATIONALITY = 'USA'
GROUP BY EDITION
ORDER BY 2 DESC
  • Related