MySQL Join from multiple options to select one value

Question

I am putting together a nice little database for adding values to options, all these are setup through a map (Has and Belongs to Many) table, because many options are pointing to a single value.

So I am trying to specify 3 option.ids and a single id in a value table - four integers to point to a single value. Three tables. And I am running into a problem with the WHERE part of the statement, because if multiple values share an option there are many results. And I need just a single result.

SELECT value.id, value.name FROM value
 LEFT JOIN (option_map_value, option_table)
 ON (value.id = option_map_value.value_id AND option_map_value.option_table_id = option_table.id)
 WHERE option_table.id IN (5, 2, 3) AND value.y_axis_id = 16;

The problem with the statement seems to be the IN on the WHERE clause. If one of the numbers are different in the IN() part, then there are multiple results - which is not good.

I have tried DISTINCT, which again works if there is one result, but returns many if there is many. The closest we have gotten to is adding a count - to return to value with the most options at the top.

So is there a way to do the WHERE to be more specific. I cannot break it out into option_table.id = 5 AND option_table.id = 2 - because that one fails. But can the WHERE clause be more specifc?

Maybe it is me being pedantic, but I would like to be able to return just the single result, instead of a count of results... Any ideas?

Answer 1

The problem with the statement seems to be the IN on the WHERE clause. If one of the numbers are different in the IN() part, then there are multiple results - which is not good. I have tried DISTINCT, which again works if there is one result, but returns many if there is many. The closest we have gotten to is adding a count - to return to value with the most options at the top.

You were very close, considering the DISTINCT:

    SELECT v.id, 
           v.name 
      FROM VALUE v
 LEFT JOIN OPTION_MAP_VALUE omv ON omv.value_id = v.id
 LEFT JOIN OPTION_TABLE ot ON ot.id = omv.option_table_id
     WHERE ot.id IN (5, 2, 3) 
       AND v.y_axis_id = 16
  GROUP BY v.id, v.name
    HAVING COUNT(*) = 3

You were on the right track, but needed to use GROUP BY instead in order to be able to use the HAVING clause to count the DISTINCT list of values.

Caveat emptor:
The GROUP BY/HAVING COUNT version of the query is dependent on your data model having a composite key, unique or primary, defined for the two columns involved (value_id and option_table_id). If this is not in place, the database will not stop duplicates being added. If duplicate rows are possible in the data, this version can return false positives because a value_id could have 3 associations to the option_table_id 5 - which would satisfy the HAVING COUNT(*) = 3.

Using JOINs:

A safer, though more involved, approach is to join onto the table that can have multiple options, as often as you have criteria:

  SELECT v.id, 
         v.name 
    FROM VALUE v
    JOIN OPTION_MAP_VALUE omv ON omv.value_id = v.id
    JOIN OPTION_TABLE ot5 ON ot5.id = omv.option_table_id
                         AND ot5.id = 5
    JOIN OPTION_TABLE ot2 ON ot2.id = omv.option_table_id
                         AND ot2.id = 2
    JOIN OPTION_TABLE ot3 ON ot3.id = omv.option_table_id
                         AND ot3.id = 3
   WHERE v.y_axis_id = 16
GROUP BY v.id, v.name