CODEWITHSUNDEEP
mysql
Vikranth Kumar
Vikranth Kumar

Reputation: 59

Execute statement in mysql if condition

i am having an issue in following query. Eveytime query runs into success condition, it directly prints the statement instead of executing. I am struck in this and i am not sure how to fix it. can anyone guide me how to fix following query

SELECT IF(
        (SELECT COUNT(*)
                FROM INFORMATION_SCHEMA.VIEWS
                WHERE table_name = 'user_details'
                AND table_schema = 'user_details'
        ) = 0 AND
        (SELECT COUNT(*)
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE table_name = 'mobile_consumer'
                AND table_schema = DATABASE()
                AND column_name IN ('payment_ref', 'pp', 'tt')
        ) = 3,
        "Select * from users_details",
        "Select 0"
)

Sql Output for above query is shown as "select * from users_details", but what i want is that that statement should execute and display rows of the table. any suggestions??

Upvotes: 1

Views: 12641

Answers (1)

Muhammad Abdullah
Muhammad Abdullah

Reputation: 916

I guess you forgot to use, THEN clause in your code. Also that you have written your statements to be executed in IF block where it should be written in THEN block. Another thing is that you have written your queries in " ", that makes them a simple string that will be printed as they are, instead write them without quotes and if you need to isolate them from rest of the code, use parenthesis. Your updated code is as given below.

IF
   (SELECT COUNT(*)
           FROM INFORMATION_SCHEMA.VIEWS
           WHERE table_name = 'user_details'
           AND table_schema = 'user_details'
   ) = 0
   AND
   (SELECT COUNT(*)
           FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'mobile_consumer'
           AND table_schema = DATABASE()
           AND column_name IN ('payment_ref', 'pp', 'tt')
   ) = 3

Then
    (Select * from users_details)
ELSE
    (Select 0)
END IF

Alternatively you can try using case statement to evaluate your conditions if you have multiple statements to execute against multiple conditions.

SELECT
    CASE WHEN 
        count1 = 0 AND count2 = 3 
    THEN 
        (Select * from users_details)
    ELSE
        (Select 0)
    END

FROM
(
    SELECT
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'user_details' AND table_schema = 'user_details') AS count1,
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'mobile_consumer' AND table_schema = DATABASE() AND column_name IN ('payment_ref', 'pp', 'tt')) AS count2
) 
    AS counts

Please mark accepted if it resolved your problem.

Upvotes: 3

Related Questions