Return a boolean value if string matches one of list MYSQL
I have 2 tables
SEQUENCES
-----------------
sequence (blob)
KNOWN_SEQUENCES
-----------------
sequence (blob)
I need to return a list of all entries in the sequences table and Id like to return a boolean if it is in the known table list
开发者_StackOverflow社区sequence known
----------------------------------
111423fa686ca 0
066787caf5671 1
See use of 'CASE' in mysql. http://dev.mysql.com/doc/refman/5.0/en/case-statement.html.
I am posting a sample here. Sorry I dont have access to a sql server right now to test this. Try to see if something like this helps.
Select s.sequence,
CASE
WHEN (select count(*) from KNOWN_SEQUENCES k where k.sequence = s.sequence) > 0 THEN '1'
ESLE '0'
END
`known`,
from SEQUENCES s;
Also, indexing the table 'KNOWN_SEQUENCES' on column 'sequence' might be better keeping performance in mind.
I did get it working, but it takes 3 seconds for only 9,000 records. That may be the best I can do. Luckily I just need to run this once.
SELECT
sequences.*,
1 as `known`
FROM
sequences, known_sequences
WHERE sequences.sequence = known_sequences.sequence
UNION
SELECT
sequences.*,
0 as `known`
FROM
sequences, known_sequences
WHERE sequences.sequence NOT IN(
SELECT known_sequences.sequence
FROM known_sequences) GROUP BY sequences.id
This might be a little long, but it should work.
SELECT
s.*,
1 as `known`
FROM
sequence AS s
INNER JOIN
known_sequences AS ks
ON
s.sequence = ks.sequence
UNION
SELECT
s.sequence,
0 AS `known`
FROM
sequence AS s
WHERE
s.sequence NOT IN
(
SELECT
s2.sequence
FROM
sequence AS s2
LEFT JOIN
known_sequences AS ks
ON
s.sequence == ks.sequence
)
精彩评论