Clever Geek Handbook

Filter for rows with column condition on array

I have a table my_table

:

id  x_id type
--- ---- ----
1    31   a
2    14   b
3    31   c
4    12   c
5    31   b
6    12   c
7    14   a

I want to return all rows where it x_id

has type

satisfying all the elements of the array arr

.

Edit: However the actual values ​​in this array and their number are unknown, but column values ​​are possible type

. There is arr

always at least one value in.

So, for arr = [b, c]

I should get the following result:

id  x_id type
--- ---- ----
3    31   c
5    31   b

How do I write a query to get a result like this?

Note. I'm not very familiar with commands sql

, so please help me change my question if it doesn't make sense.

+3


source to share


3 answers 


select *
  from my_table
 where x_id in (select x.x_id
                  from (select x_id from my_table where type = 'b') x
                  join (select x_id from my_table where type = 'c') y
                    on x.x_id = y.x_id)
   and type in ('b', 'c')

Script test: http://sqlfiddle.com/#!2/f8601/2/0

This might better match variables (having one variable to hold a list of types in the format "b", "c" and another variable to hold the count of types. Is there a way to get a variable to store what's in the array like this value 'b','c'

:?



select *
  from my_table
 where x_id in (select x_id
                  from my_table
                 where type in ('b', 'c')
                 group by x_id
                having count(distinct type) = 2)
   and type in ('b', 'c')

Fiddle: http://sqlfiddle.com/#!2/f8601/12/0

So, you have to use the variable in 2 type in ()

places (variable inside ()

) and the variable containing the count instead of 2.

+4


source


Here's a slightly ugly way to do it:



SELECT * FROM my_table
WHERE x_id IN
(
    SELECT x_id FROM
    (
        SELECT x_id, type
        FROM my_table x
        WHERE type IN ('b', 'c')
        GROUP BY x_id, type
    ) y
    GROUP BY x_id
    HAVING COUNT(x_id) = 2
) AND type IN ('b', 'c')
+1


source 


SELECT 
  * 
FROM 
  `my_table` 
WHERE 
  `type` IN ("b","c")
0


source






More articles:


All Articles