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.
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','%')
'mountain','mountain','space': Instead use HAVING SUM(b.keyboardName='mountain') AND SUM(b.keyboardName='picture') AND SUM(b.keyboardName='space')
mountains are different?HAVING COUNT(distinct tag)=3
like concat('%'... with 'RLIKE CONCAT('(^|[ ,])', 'space', '(,|$)'). That will match space wherever it appears in the OP's (inconsistent!) array catenation, and won't match everything %space% would (spaces and triple-space, for instance).