CODEWITHSUNDEEP
mysqlsqlif-statementexists
Rs2845
Rs2845

Reputation: 53

MySQL IF EXISTS not working

Trying to use IF EXISTS to automatically choose which table to select a record from.

I just get the following error message with the code below.

Where am I going wrong?

IF EXISTS (SELECT 1 FROM Users WHERE Reference='USR00000007')
    SELECT * FROM Users WHERE Reference='USR00000007'
ELSE
    SELECT * FROM Staff WHERE Reference='USR00000007';

Error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS (SELECT 1 FROM Users WHERE Reference='USR00000007') SELECT * FROM' at line 1

Upvotes: 0

Views: 2681

Answers (2)

Adam Silenko
Adam Silenko

Reputation: 3108

if you want union of two query, both mast have same number of fields (if you need, you can add null fields to query) and fields should have similar type, so try this:

SELECT Users.id as user_id, null as staff_id, some_other_number_field, some_other_char_field
FROM Users 
WHERE Reference='USR00000007'
SELECT null , Staff.id, some_other_number_field, some_other_char_field 
FROM Staff 
WHERE Reference='USR00000007'
and not EXISTS (SELECT 1 FROM Users WHERE Reference='USR00000007');

maybe you need query like this, to always get only users for Reference:

SELECT * 
FROM Users 
WHERE (Reference='USR00000007'
or exists (
  SELECT 1 
  FROM Staff 
  WHERE Reference='USR00000007'
  and Staff.user_id = Users.id));

but you must have join condition similar to "and Staff.user_id = Users.id"

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The problem is in MySQL. The IF -- as control flow -- only works in programming blocks such as stored procedures, stored functions, and triggers.

Assuming the columns are the same in both tables, you can do what you want as a single query:

SELECT u.*
FROM Users u
WHERE u.Reference = 'USR00000007'
UNION ALL
SELECT s
FROM Staff s
WHERE s.Reference = 'USR00000007' AND
      NOT EXISTS (SELECT 1 FROM Users u2 WHERE u2.Reference = 'USR00000007')

Upvotes: 3

Related Questions