Search code examples
mysqlsqlinner-joinaggregate-functionshaving-clause

sql many-to-many query result only if matches only and exactly all items in array


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!


Solution

  • 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.