IF condition in mysql

Question

I have a contact table I wish to query when a certain condition exists. I tried the query below but am getting a syntax error.

SELECT * 
FROM   contact_details 
WHERE  contactDeleted` =0 
AND    IF ( contactVisibility = "private"
           ,  SELECT * FROM contact_details
              WHERE    contactUserId = 1 
           , IF( contactVisibility = "group"
                ,  SELECT * FROM contact_details  
                   WHERE contactGroup = 3 
               ) 
          )

Is that your exact SQL? There's a back tick character after `contactDeleted` that doesn't have a mate — nybbler, Feb 16 '12 at 06:00
You better described what you want by words, because wrong sql query literally explains nothing — zerkms, Feb 16 '12 at 06:00
i'm sure you're getting any erors if the query doesn't work - it would be really helpful to post the error-messages and -codes. the table-definitions of the tables involved would also be useful. — oezi, Feb 16 '12 at 06:10
@zerkms No, wouldn't say that's the *only* point of confusion (of course), but I think I've deciphered what Shankar is actually looking for. I only asked about the backtick because if the SQL wasn't copied/pasted and has other typos it's going to be even more impossible to help out with the lack of info. ;) — nybbler, Feb 16 '12 at 06:17
yes i am getting syntax error on line 2 that is on ware condition near `AND IF ( contactVisibility = "private", SELECT * FROM contact_details` — Shankar Swamy, Feb 16 '12 at 06:18
@nybbler: I'm really amazed how you were possible to understand what he needs ;-) — zerkms, Feb 16 '12 at 06:18

nybbler · Accepted Answer · 2012-02-16T06:28:45.513

1

If I'm understanding your question correctly (which is difficult with the lack of info you've provided. Sample datasets and expected outcomes are typically helpful), then I don't believe you need IFs at all for what you want. The following will return contacts that are not deleted and who either have (visibility = "private" and userId = 1) OR (visibility = "group" and group = 3)

SELECT * 
FROM contact_details 
WHERE contactDeleted = 0 
    AND ( 
        (contactVisibility = "public") 
        OR
        (contactVisibility = "private" AND contactUserId = 1) 
        OR 
        (contactVisibility = "group" AND contactGroup = 3)
    )

edited Feb 16 '12 at 06:28

answered Feb 16 '12 at 06:15

nybbler

4,793
28
23

Their are 3 type of contact details in one table. public,private and group, if the contact is public their is no need any condition. if the contact is private that contact should we mine(that is should have my id in contactUserId). if group their will be many group in other table that groupId value will be saved in contactGroup and it should check weather that contact match my group. – Shankar Swamy Feb 16 '12 at 06:23
@ShankarSwamy These notes should be in your question. Is your contactUserID = 1 then and your contactGroup = 3, or do these need to be searched for based off some other piece? I've updated to account for contactVisibility = "public" – nybbler Feb 16 '12 at 06:27

score 0 · Answer 2 · edited May 23 '17 at 12:11

I am assuming you want to use the IF() function and not the statement which is for stored functions..

Refer to this link for more information on that.

Notice that you have put 2 select statements in there, where the custom return values are supposed to be. So you are returning a SELECT *... now notice that in your upper level sql statement you have an AND.. so you basically writing AND SELECT *.. which will give you the syntax error.

Try using .. AND x IN (SELECT *) .. to find if x is in the returned values.

Let me also list this link to make use of an existing and well written answer which may also applicable to your question.

IF condition in mysql

2 Answers2