MySQL – Select Data Where Column Value Exists in Separate Rows

MySQLselect

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

Best Answer

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.

Related Solutions

Mysql – How to select/count rows where the same value exists in a column but not in other column all at the same table

SELECT A.*
FROM default_relations_users A
LEFT JOIN default_relations_users B
ON  A.id_user_rq = B.id_user_ap
AND A.id_user_ap = B.id_user_rq
WHERE B.id_user_rq IS NULL;

I loaded you sample data (I added an additional index)

mysql> use Reynierpm
Database changed
mysql> DROP TABLE IF EXISTS `default_relations_users`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `default_relations_users` (
    ->   `id_user_rq` int(11) NOT NULL,
    ->   `id_user_ap` int(11) NOT NULL,
    ->   UNIQUE KEY `rusers_rq_ap_idx` (`id_user_rq`,`id_user_ap`),
    ->   UNIQUE KEY `rusers_ap_eq_idx` (`id_user_ap`,`id_user_rq`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `default_relations_users` (`id_user_rq`, `id_user_ap`) VALUES
    -> (1, 2), (1, 3), (1, 4), (2, 1), (2, 2), (2, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from default_relations_users;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          2 |
|          1 |          3 |
|          1 |          4 |
|          2 |          1 |
|          2 |          2 |
|          2 |          3 |
+------------+------------+
6 rows in set (0.00 sec)

mysql>

Here is the result of my answer

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
|          2 |          3 |
+------------+------------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL
    -> AND A.id_user_rq = 1;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
+------------+------------+
2 rows in set (0.01 sec)

mysql>

MySQL – How to Select Specific Rows if a Column Exists or All Rows if a Column Doesn’t

My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach.

There is one other, tricky way though that works no matter if the table has or not such a column:

SELECT 
  ( SELECT COUNT(*)
    FROM TableName AS t
    WHERE active = 1
  ) AS cnt
FROM
  ( SELECT 1 AS active
  ) AS dummy ;

Tested at SQL-Fiddle How it works?

If the table has a column named active, the query is "translated" as if it had:

    WHERE t.active = 1

If the table doesn't have a column named active, the query is "translated" as if it had:

    WHERE dummy.active = 1         -- which is true

Best Answer

Related Solutions

Mysql – How to select/count rows where the same value exists in a column but not in other column all at the same table

MySQL – How to Select Specific Rows if a Column Exists or All Rows if a Column Doesn’t

Related Question