CODEWITHSUNDEEP
mysql
Ali Zia
Ali Zia

Reputation: 3875

I want to return a record based on the condition

I am trying to execute a simple if else query based on mysql

SELECT IF EXISTS (SELECT nickname FROM contacts WHERE contacts.user_id = 47 AND contacts.contact_id = 69)
BEGIN
    SELECT nickname FROM contacts WHERE contacts.user_id = 47 AND contacts.contact_id = 69
END
ELSE
BEGIN
    SELECT CONCAT(users.firstname," ",users.lastname) AS nickname FROM users WHERE users.id = 69
END

But it's giving me an error

Error Code: 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 'EXISTS (SELECT nickname FROM contacts WHERE contacts.user_id = 47 AND contacts.c' at line 1

Can anyone guide me what's the issue here?

Upvotes: 0

Views: 30

Answers (1)

sticky bit
sticky bit

Reputation: 37472

You cannot use control flow statements, like IF, in queries.

However you can achieve what you apparently want to do with a UNION ALL of two queries, one of them returning the empty set depending on your condition.

SELECT nickname
       FROM contacts
       WHERE contacts.user_id = 47
             AND contacts.contact_id = 69
UNION ALL
SELECT concat(users.firstname, ' ', users.lastname) nickname
       FROM users
       WHERE users.id = 69
             AND NOT EXISTS (SELECT *
                                    FROM contacts
                                    WHERE contacts.user_id = 47
                                          AND contacts.contact_id = 69);

Also don't use double quotes for string literals. Yes, MySQL does accept that but in standard SQL single quotes are meant for that job. And it doesn't harm to get used to that and produce more portable code.

Upvotes: 2

Related Questions