MySQL Select Within If Statement

IF' in 'SELECT' statement - choose output value based on column values

SELECT id, 
IF(type = 'P', amount, amount * -1) as amount
FROM report

See http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html.

Additionally, you could handle when the condition is null. In the case of a null amount:

SELECT id, 
IF(type = 'P', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount
FROM report

The part IFNULL(amount,0) means when amount is not null return amount else return 0.

Using an IF Statement in a MySQL SELECT query

The IF/THEN/ELSE construct you are using is only valid in stored procedures and functions. Your query will need to be restructured because you can't use the IF() function to control the flow of the WHERE clause like this.

The IF() function that can be used in queries is primarily meant to be used in the SELECT portion of the query for selecting different data based on certain conditions, not so much to be used in the WHERE portion of the query:

SELECT IF(JQ.COURSE_ID=0, 'Some Result If True', 'Some Result If False'), OTHER_COLUMNS
FROM ...
WHERE ...

MySQL IF ELSEIF in select query

You have what you have used in stored procedures like this for reference, but they are not intended to be used as you have now. You can use IF as shown by duskwuff. But a Case statement is better for eyes. Like this:

select id, 
(
CASE
WHEN qty_1 <= '23' THEN price
WHEN '23' > qty_1 && qty_2 <= '23' THEN price_2
WHEN '23' > qty_2 && qty_3 <= '23' THEN price_3
WHEN '23' > qty_3 THEN price_4
ELSE 1
END) AS total
from product;

This looks cleaner. I suppose you do not require the inner SELECT anyway..

SELECT inside IF statement

It's likely a table alias issue you're seeing, but you can rewrite this using a LEFT JOIN:

SELECT      J.jargon as jargon, 
J.description as description,
J.example as example,
R.jargon As rootJargonName
FROM jargons as J
LEFT JOIN users as U ON U.id = J.addedBy
LEFT JOIN jargons as R ON J.rootJargon = R.ID
ORDER BY J.id DESC

If you would like to keep the IF() function, you can use the following, however I recommend using the LEFT JOIN.

SELECT      J.jargon as jargon, 
J.description as description,
J.example as example,
IF(J.rootJargon != 0, (SELECT jargon FROM jargons as R WHERE R.id = J.rootJargon), NULL) as rootJargonName
FROM jargons as J
LEFT JOIN users as U ON U.id = J.addedBy
ORDER BY J.id DESC

Select query in IF statement MYSQL

Using IF and subquery in MySQL

SELECT table1.column1, table1.column2,

(

SELECT IF (
(SELECT column3 FROM table1 WHERE column3 IN (SELECT column FROM table2)), 1, 0
)

) AS column_output

FROM table1

Mysql Select within IF condition

why not use a left join and use fieldX=3 as a join condition? if fieldX is different from 3, sql fills the field with NULL

select a.field1, a.field2, sub.value
from abc a
left join
(
select value from sub_table
where cat_id = 3
limit 0,1
) sub
on a.fieldX = 3

or, if you do want to get all rows for the corresponding values (i see you have cat_id = 3 and fieldX = 3, so basically cat_id = fieldX), just use a simple join. no need to use complicated if constructs. sql was built to do fast and efficient joins:

select a.field1, a.field2, sub.value
from abc a
left join sub_table sub
on a.fieldX = sub.cat_id

note however, that the second query will return multiple rows, when there are more matches between fieldX and cat_id (non-unique cat_id)

Execute statement in mysql if condition

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.

How to use select inside if for MySQL

insert into tbl_disease(nid, name, diagnosed_time, treatment_times, inherited) 
values ('314759',
'high blood pressure',
'2015-07-01',
'4',
CASE WHEN EXISTS ( select NULL
from tbl_disease
where disease_name='high blood pressure' )
THEN 'Y'
ELSE 'N'
END);

MySQL IF(SELECT) statement

For your first example, you're not evaluating the condition. You need to move the IS NULL outside the SELECT:

IF (SELECT 'id' FROM terms WHERE name = 'thename') IS NULL THEN
...

If you're running this inside a SELECT statement, you may want to use CASE:

SELECT
CASE
WHEN (SELECT 'id' FROM terms WHERE name = 'thename') IS NULL THEN ...
END

Otherwise, see the IF documentation.



Related Topics



Leave a reply



Submit