CODEWITHSUNDEEP
mysqlselect
bobobobo
bobobobo

Reputation: 67244

Select back things that don't exist

I have this table. I want to select 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?

Upvotes: 1

Views: 428

Answers (2)

amphetamachine
amphetamachine

Reputation: 30595

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);

Upvotes: 1

luke
luke

Reputation: 14788

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.

Upvotes: 4

Related Questions