I have three tables on a mysql database:
| pieces | line_up | instrument |
--------------------------------------------
| id_piece | piece_id | id_instrument |
| title | instrument_id | instrument |
now what I'm trying to achieve is: I'd like to query those pieces whose line_up is made up by exactly the instruments given by a list, not one less not one more. This sql query reduces the result to those piece who are only played by the 2 instruments, but it includes the solos
SELECT id_piece, title FROM pieces WHERE
(SELECT COUNT(*) FROM line_up WHERE line_up.piece_id = pieces.id_piece)
=
(SELECT COUNT(*) FROM line_up
INNER JOIN instruments ON instruments.id_instrument = line_up.instrument_id
WHERE line_up.piece_id = pieces.id_piece
AND instruments.instrument IN ('guitar', 'drums'));
For example with these tables:
| pieces | | line_up | | instruments |
----------------------- --------------------------- ------------------------------
| id_piece | title | | piece_id | instrument_id | | id_instrument | instrument |
----------------------- ---------------------------- ------------------------------
| 1 | hello | | 1 | 1 | | 1 | guitar |
| 2 | goodbye | | 1 | 2 | | 2 | drums |
| 3 | goodnight | | 2 | 1 | ------------------------------
------------------------ | 3 | 2 |
----------------------------
the only actual piece for both guitar and drums, hence the result of my query, should be 1 | hello
.
Any suggestions? Thank you!
You could use aggregation like so:
select p.id_piece, p.title
from pieces as p
inner join line_up as l on l.piece_id = p.id_piece
inner join instruments as i on l.instrument_id = i.id_instrument
group by p.id_piece
having sum(i.instrument in ('guitar', 'drums')) = 2
and sum(i.instrument not in ('guitar', 'drums')) = 0
If you don't have too many instruments to search for, an alternative is string aggregation in the having
clause:
having group_concat(i.instrument order by i.instrument) = 'drums,guitar'
This second expression requires that you give the query an alphabetically-ordered list of instruments.