using in operator with subquery

Question

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`;

You have lots of implicit joins in your query, why do you say that you haven't used any joins? — Barmar
– Barmar, Commented May 14, 2014 at 10:19
Key_Name is from musical_keys? Song_Title is from record? Yes? Why do you display a random song title with the musical key and its record count anyway? — Thorsten Kettner
– Thorsten Kettner, Commented May 14, 2014 at 11:04
i also did use joins but wasn't sure how to fit the subquery in them! so in order to fit the subquery i went for the one without joins!! — Atif
– Atif, Commented May 14, 2014 at 11:12

Gordon Linoff · Accepted Answer · 2014-05-14 10:46:12Z

1

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;

answered May 14, 2014 at 10:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thorsten Kettner · Accepted Answer · 2014-05-15 15:02:02Z

0

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.

edited May 15, 2014 at 15:02

answered May 14, 2014 at 15:00

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

7 Comments

Thorsten Kettner Over a year ago

And just a remark: LIMIT 3 gives you three top keys, but there could be more of course (e.g. there can be four keys with exactly 1000 Songs). In this case the result list would be rather random.

Atif Over a year ago

yeah i figured that out when i used the same technique in another query!!! but this is the only solution i could come up with!! if you know a better way then please do share!!

Thorsten Kettner Over a year ago

Hi Atif. First of all: My simple query is given correct results, yes? No need for all those joins and accessing a table more than once. Or was I mistaken? Have you tried my query? So provided my original query is right, you can enhance it such that it retrieves more records in case of a tie. I've edited my answer accordingly.

Atif Over a year ago

forgive me for being naive, but your query generates this error message: Error Code: 1248 Every derived table must have its own alias I also changed the names according to my table names but the error still remains!! I'm a beginner and this is my first course of Data Base so I don't know that much!!

Thorsten Kettner Over a year ago

Well, I guess that means that the (SELECT COUNT ...) needs a name in MySQL, so make it (SELECT ... (SELECT COUNT ...) dummy) ORDER BY KeyName. Maybe even the outer select needs one? I don't know. That would be (SELECT ... (SELECT COUNT ...) dummy1) dummy2 ORDER BY KeyName . But I believe you will only have to Name the inner one with SELECT COUNT. I'll update my answer accordingly.

|

Barmar · Accepted Answer · 2014-05-14 10:25:40Z

0

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

answered May 14, 2014 at 10:25

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

using in operator with subquery

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related