CODEWITHSUNDEEP
mysqlsql
Atif
Atif

Reputation: 159

using in operator with subquery

i have been trying to use this particular query to find the top three most used musical keys in songs and show all the songs made using those musical keys so far the problem is that i'm using in operator with a subquery and it doesn't work!! i have read that instead of in using join is preferable but since i haven't used any joins i am not able to use that with the query!! plaeae help!!!

SELECT `Key_Name`,`Song_Title` 
FROM `musicalkey_record`,`musical_keys`,`record` 
WHERE `record`.`Record_ID`=`musicalkey_record`.`Record_ID`
AND `musical_keys`.`Key_ID`=`musicalkey_record`.`Key_ID` 
AND `Key_Name` IN (SELECT `Key_Name` FROM `musicalkey_record`,`musical_keys`,`record`     
WHERE `record`.`Record_ID`=`musicalkey_record`.`Record_ID`
AND `musical_keys`.`Key_ID`=`musicalkey_record`.`Key_ID` GROUP BY `Key_Name` ORDER BY   
COUNT(`Song_Title`) DESC LIMIT 3) ORDER BY `Key_Name`;

query with joins but without subquery:

SELECT `Key_Name`,`Song_Title` FROM `musical_keys` INNER JOIN `musicalkey_record` ON   
`musical_keys`.`Key_ID`=`musicalkey_record`.`Key_ID`
INNER JOIN `record` ON `record`.`Record_ID`=`musicalkey_record`.`Record_ID` AND `   
Key_Name` IN ('4F','Circle of fifths','C-Major') ORDER BY `Key_Name`;

Upvotes: 0

Views: 91

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

I may be wrong, but it seems all those joins are not necessary. You want to count song records per musical key. So join musical_keys with musicalkey_record and count. To get the song names you would have to join with record, too, and use wm_concat to get the song names in one string.

SELECT *
FROM
(
  SELECT mk.Key_Name, GROUP_CONCAT(r.Song_Title) as Song_Titles
  FROM musical_keys mk
  LEFT JOIN musicalkey_record mkr ON mkr.Key_ID = mk.Key_ID 
  LEFT JOIN record r ON r.Record_ID = mkr.Record_ID 
  GROUP BY mk.Key_Name 
  ORDER BY COUNT(*) DESC
  LIMIT 3
) dummy
ORDER BY Key_Name;

EDIT: If you want to show all equally ranking records, i.e. at least three, but more if record four or more have the same count as record three, then you would have to get the top three, look up the third place and then select again to get all records with at least that count.

SELECT mk.Key_Name, GROUP_CONCAT(r.Song_Title) as Song_Titles
FROM musical_keys mk
LEFT JOIN musicalkey_record mkr ON mkr.Key_ID = mk.Key_ID 
LEFT JOIN record r ON r.Record_ID = mkr.Record_ID 
GROUP BY mk.Key_Name
HAVING COUNT(*) >=
( 
  SELECT MIN(cnt)
  FROM
  (
    SELECT COUNT(*) as cnt
    FROM musical_keys mk
    LEFT JOIN musicalkey_record mkr ON mkr.Key_ID = mk.Key_ID 
    GROUP BY mk.Key_Name 
    ORDER BY COUNT(*) DESC
    LIMIT 3
  ) dummy
)
ORDER BY Key_Name;

I suppose that the Key_Name is unique in musical_keys? Then you can even remove musical_keys from the inner select altogether and only select from musicalkey_record grouping by mkr.Key_ID instead of mk.Key_Name. Thus the query is even shorter.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is a simplification of Barmar's approach, reducing the number of joins:

SELECT mk.Key_Name, Song_Title
FROM musicalkey_record mr JOIN
     musical_keys mk
     ON mk.Key_ID = mr.Key_ID JOIN
     record r
     ON r.Record_ID = mr.Record_ID JOIN
     (SELECT Key_ID
      FROM musicalkey_record mr
      GROUP BY Key_ID
      ORDER BY COUNT(*) DESC
      LIMIT 3
     ) top3
     ON mr.Key_ID = top3.Key_ID
ORDER BY mk.Key_Name;

Upvotes: 1

Barmar
Barmar

Reputation: 780673

SELECT mk.Key_Name, Song_Title
FROM musicalkey_record AS mr
JOIN musical_keys AS mk ON mk.Key_ID = mr.Key_ID
JOIN record AS r ON r.Record_ID = mr.Record_ID
JOIN (SELECT Key_Name
      FROM FROM musicalkey_record AS mr
      JOIN musical_keys AS mk ON mk.Key_ID = mr.Key_ID
      JOIN record AS r ON r.Record_ID = mr.Record_ID
      GROUP BY Key_Name
      ORDER BY COUNT(Song_Title) DESC
      LIMIT 3) AS top3 ON mk.Key_Name = top3.Key_Name
ORDER BY mk.Key_Name

Upvotes: 0

Related Questions