Rimble Rimble - 28 days ago 9
MySQL Question

How do I select an unique value from a column based on multiple IDs in a many-to-one situation?

I'm trying to create a system in PHP where I can send automated answers based on the information from their orders and requirements for these answers. For instance, if a customer has an order with product_id 20,30 and 40 in it. I want to be able to set up a requirement for one of the answers, do a check if it matches and then send out the corresponding answer.

Currently there are two tables, one for the message itself and one for the requirements linked to these messages.

tickets_answers

id name message
1 Test 1 This is the first test message.
2 Test 2 Another test message


tickets_answers_options

id answer_id option_type option_id
1 1 product_id 20
2 1 product_id 30
3 1 product_id 40
4 2 product_id 25
5 2 product_id 30


Looping through all available answers and see if they match is not preferred since I do not know how many answers/requirements will be set up in the future. Which is why I'm trying to retrieve the corresponding answer based on the known product IDs shown in the second table as option_ids.

The query will be built in PHP. I will most likely do an implode or build the query based on the info from the order itself. I'm just tryin to sort out the SQL part for now.

I have tried the following query to retrieve the answer_id

SELECT answer_id
FROM tickets_answers_options
WHERE option_id IN (SELECT option_id
FROM tickets_answers_options
WHERE option_type = 'product_id'
AND option_id = 20
OR option_id = 30
OR option_id = 40)
GROUP BY answer_id


This results in something close to what I want. This query returns the answer_ids

1
2


In this case the 2 popped up because it matched with one of the option_id mentioned in the sub-query.

My desired result would be get a single answer_id matching to the option_ids.
I see that this might be a bit backwards of how I have set up the structure for this particular system. But in the future there will be multiple option_types to check for. Right now I'm trying to do a check for only product_id, in PHP I can set up multiple queries for the different option_types so make sure everything matches.

I'm hoping I posted enough relevant information here, if not please let me know so I can provide more. And thank you for your time in advance looking through this.

Answer

Not very elegant, but you can build a subquery per product id and join all of them:

SELECT a.answer_id 
FROM (SELECT t.answer_id
    FROM tickets_answers_options t 
    WHERE t.option_type = 'product_id'
    AND t.option_id = 20) as a 

    JOIN (SELECT t.answer_id
    FROM tickets_answers_options t 
    WHERE t.option_type = 'product_id'
    AND t.option_id = 30) as b 
ON a.answer_id = b.answer_id

JOIN (SELECT t.answer_id
    FROM tickets_answers_options t 
    WHERE t.option_type = 'product_id'
    AND t.option_id = 40) as c
ON a.answer_id = c.answer_id

EDIT

The option above can create quite a lot of subqueries. Here's another option that may have better performance and is more elegant.

Note that you will always need to feed it the product IDs for the HAVING clause sorted, this way: 20,30,40 and not this way: 30,20,40 for it to work.

SELECT t.answer_id, GROUP_CONCAT(DISTINCT(t.option_id) ORDER BY t.option_id) AS option_ids_found
FROM tickets_answers_options t 
WHERE t.option_type = 'product_id'
AND t.option_id in (20,30,40)
GROUP BY t.answer_id
HAVING option_ids_found = '20,30,40'
Comments