CODEWITHSUNDEEP
mysqlif-statementunion
Chris
Chris

Reputation: 5617

Append to MySQL Results if 1 column in NOT IN the results

I want to create a MyQSL Query similar to 

SELECT person, city FROM mytable 
UNION 
SELECT 'BOB', 'Chicago' IF 'BOB' NOT IN (SELECT person FROM mytable);

If 'BOB' is not returned in the results, I want to append him to the results and list him as being in Chicago. If BOB does come back in the results, no matter what his location is, I do not want to append him as being in Chicago.

I can make this work if I exactly match the columns, but I will end up getting multiple results for BOB if he is listed as being somewhere other than Chicago.

SELECT person, city FROM mytable
UNION
SELECT 'BOB', 'Chicago'

but I do not want to match on the location. Just the person's name.

Upvotes: 1

Views: 61

Answers (2)

Jose Rui Santos
Jose Rui Santos

Reputation: 15319

This should work:

SELECT person, city FROM mytable 
UNION 
SELECT 'BOB', 'Chicago' from dual
 where NOT exists (SELECT person FROM mytable WHERE person = 'BOB');

A more optimized version, that returns the same results

SELECT person, city FROM mytable WHERE person <> 'BOB'
UNION 
SELECT 'BOB', COALESCE((select city from mytable WHERE person = 'BOB'), 'Chicago') from dual

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65547

Here's a rewrite of your original query that should work:

SELECT person, city FROM mytable 
UNION 
SELECT 'BOB', 'Chicago' from dual where not exists (
  SELECT NULL FROM mytable where person = 'BOB'
);

Upvotes: 0

Related Questions