1

This is on a MySQL database. Locations have services.

What I want is to get all results where service_id is 2 AND 3, not OR so using IN is out I believe.

Database

----------------------------
| location_id | service_id |
----------------------------
|      1      |      2     |
|      1      |      3     |
|      2      |      2     |
----------------------------

Expected Results

----------------------------
| location_id | service_id |
----------------------------
|      1      |      2     |
|      1      |      3     |
----------------------------
1

Here's your query:

SELECT
   T.location_id,
   GROUP_CONCAT(T.service_id) AS serviceids
FROM `Table` AS T
GROUP BY T.location_id
HAVING serviceids IN ('2,3');

You could use ORDER BY inside the GROUP_CONCAT():

SELECT 
   T.location_id,
   GROUP_CONCAT(T.service_id ORDER BY service_id ASC) AS serviceids
FROM `Table` AS T
GROUP BY T.location_id
HAVING serviceids IN ('2,3');

EDIT, Another way:

SET @LOCATION_ID=0;
SET @LOCATION_ID=(
    SELECT
        T.location_id
    FROM `Table` AS T
    GROUP BY T.location_id
    HAVING GROUP_CONCAT(T.service_id ORDER BY T.service_id ASC) LIKE '%2,3%');
SELECT
    T.location_id,
    T.service_id
FROM `Table` AS T
WHERE T.service_id IN (2,3) AND T.location_id=@LOCATION_ID;

Result:

enter image description here

Hope this help.

  • What if the ID's are not in order, do I just always do an order by T.service_id? – HeathN Jul 1 '15 at 4:59
  • You can make this work better with one Stored Procedure. – oNare Jul 1 '15 at 11:08
  • I recommend increasing the group_concat_max_len at least for this session: "SET SESSION group_concat_max_len = 1000000;" – aowie1 Jul 8 '15 at 15:37
0

Here's a kludgy way:

SELECT location_id,
       GROUP_CONCAT(service_id) AS service_ids
    FROM tbl
    GROUP BY location_id
    HAVING service_ids = '2,3';
  • What if the result of GROUP_CONCAT() comes out as '3,2'? – mustaccio Jul 1 '15 at 0:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.