Select back things that don't exist
I have this table. I want to sel开发者_高级运维ect back the items that don't exist already, so I can create them.
table tags +---------+-------+ | tagId | name | | 1 | C | | 2 | DX | | 3 | CG |
Say SQL looks like:
select name from tags where name in ( 'C', 'CG', 'RX' )
You get back 'C'
and 'CG'
, so you know you have to create 'RX'
.
Is there a way to get a MySQL statement like this to return 'RX'
instead, when 'RX'
doesn't already exist?
lets assume your tags ('c', 'cg','rx') are in a table called tags_match with the same structure as above
then you could do this:
select tr.name
from tags as tl
right join tags_match as tr
on tl.name = tr.name
where tl.name is null
This will find all the items in tags_match that are not in tags, so this would give you the desired result, but unfortunately your tags ('c', 'cg','rx') are not in a table :(
No matter we can use a subquery to 'fake' the table
select tr.name
from tags as tl
right join (select 'cg' as name
union select 'c' as name
union select 'rx' as name) as tr
on tl.name = tr.name
where tl.name is null
Although its a little ugly, this will work. If you have lots of items you want to test you may want to consider creating a real temporary table.
Sure!
select name from tags where name not in ( 'C', 'CG', 'RX' )
Only problem is, that if the not in
list is long, this query will be slow because it'll have to check each element against each row's 'name'
field in order to determine if it's not in the list, as opposed to an in
where, when it matches, it'll stop checking the list at that point and return the row.
Edit: The above solution is wrong. I am very sorry. The real solution is below.
Here is how you do it in one line of SQL:
select name from (
select "C" as name
union select "CG"
union select "RX"
) as foo
where name not in (select name from tags);
精彩评论