CODEWITHSUNDEEP
mysqlsqlselect
user1676376
user1676376

Reputation: 31

mysql select where array in array

i am doing a table with multiple keyboards or tags, like this:

id |   keyboards
===+================================================
1  |     picture,mountain,animals 
2  |     water,mountain
3  |     sound, mountain
4  |     water, picture, mountain, space, other

and i want to make a select.

SELECT id from table where (mountain,picture,space) in keyboards;

result must be: 1 row

id

4.

please help me.

Upvotes: 1

Views: 243

Answers (1)

John Woo
John Woo

Reputation: 263713

You have poor database normalization. Best way to do is to create a table Keyboards 

CREATE TABLE Keyboards
(
    Keyboard_ID INT AUTO_INCREMENT,
    KeyBoardName VARCHAR(25),
    CONSTRAINT kbrd_pk PRIMARY KEY (Keyboard_ID),
    CONSTRAINT kbrd_uq UNIQUE(KeyboardName)
)

CREATE table Tags
(
    ID INT AUTO_INCREMENT,
    Keyboard_ID int,
    CONSTRAINT tags_pk PRIMARY KEY (ID),
    CONSTRAINT tags_pk FOREIGN KEY (keyboard_ID) REFERENCES keyboards(keyboard_ID)
)

then reference keyboards.id in tags table.

Then you can do this query,

SELECT id,
FROM   tags a
       INNER JOIN keyboards b
           ON a.keyboard_id = b.keyboard_ID
WHERE  b.keyboardName IN ('mountain','picture','space')
GROUP BY ID
HAVING COUNT(DISTINCT b.keyboardName) = 3

but to answer you question, you can simply do this

select *
from tags
where keyboards like concat('%','mountain','%') AND
      keyboards like concat('%','picture','%') and
      keyboards like concat('%','space','%')

SQLFiddle Demo

Upvotes: 2

Related Questions