CODEWITHSUNDEEP
mysql
Paul N
Paul N

Reputation: 109

MySQL Get grouped join from multiple ID's

I'm creating a database for compressed files with some media files. To connect the files within every .zip, i'me using a middle table compressed_has_medium.

The Database

medium           compressed_has_medium             compressed
id | filename    id | medium_id | compressed_id    id | filename
--------------   ------------------------------    -------------
 1 | file1.mp3    1 |  1        |  1                1 | compressed1.zip
 2 | file2.mp3    1 |  2        |  1                2 | compressed2.zip
 3 | file3.mp3    1 |  3        |  1                3 | compressed3.zip
 4 | file4.mp3    1 |  4        |  1
 5 | file5.mp3    1 |  5        |  1
 6 | file6.mp3    1 |  6        |  1
 7 | file7.mp3    1 |  1        |  2
 8 | file8.mp3    1 |  2        |  2
 9 | file9.mp3    1 |  3        |  2

The Problem

I need to return every .zip that contain the ID's that I send to MySQL. If the zip1 has the files 1,2,3,4,5,6 and I want the zip for the files 3,4, it should return me the zip1. But if the zip2 has the files 1,2,3 and I request the files 3,4, it shouldn't be returned.

The partial solution

I've tried a lot and get to this solution: http://sqlfiddle.com/#!9/cb0864/37 but It's a really ugly query and probably not efficient.

SELECT c.*, GROUP_CONCAT(cm.medium_id) as media
FROM compressed as c
INNER JOIN compressed_has_medium as cm
ON cm.compressed_id = c.id
GROUP BY cm.compressed_id
HAVING (media LIKE '%,3,%' OR media LIKE '3,%' OR media LIKE '%3')
AND (media LIKE '%,4,%' OR media LIKE '4,%' OR media LIKE '%,4')

Do you know a better way to do it?

Many tanks!

Upvotes: 1

Views: 26

Answers (1)

Arun pandian M
Arun pandian M

Reputation: 882

May be this is what you expecting i believe

i have changed the query in your fiddle : http://sqlfiddle.com/#!9/cb0864/62

SELECT c.*,GROUP_CONCAT(cm.medium_id) as media
FROM compressed as c
LEFT JOIN compressed_has_medium as cm 
ON cm.compressed_id = c.id
WHERE cm.medium_id in (3,4)
GROUP BY cm.compressed_id 
having sum(cm.medium_id in (3,4)) = 2 and   // 2 represents the numbers of medium id you are entering
sum(cm.medium_id not in (3,4)) = 0

Upvotes: 1

Related Questions