Hi I want select query bases on some condition as below. How can I achieve ?
if var1>var2 then
SELECT * from table1;
ELSE
SELECT * from table2;
end if
Thanks in Advance.
2 Answers
create a STORED PROCEDURE on this,
DELIMITER $$
CREATE PROCEDURE procName(IN _val1 INT, IN _val2 INT)
BEGIN
IF _var1 > _var2 THEN
SELECT * from table1;
ELSE
SELECT * from table2;
END IF;
END $$
DELIMITER ;
and when you call the procedure,
CALL procName(1,2)
1 Comment
hemant
Thanks. but I'm restricted to query only. I can't create store Procedure. Moreover I want some insert statement in Else condition and want to return the new inserted record.
Something like this?
SELECT * from table1 WHERE var1>var2
UNION ALL
SELECT * from table2 WHERE var1<=var2
Edit: based on your comment, maybe you need something like this query:
SELECT Column1, Column2, Column3
FROM (
SELECT 1 as Tab, Column1, Column2, Column3
FROM Table1
UNION ALL
SELECT 0 as Tab, 'Value1', 'Value2', 'Value3'
) s
WHERE Tab = ((SELECT COUNT(*) FROM Table1)>10)
this will return all rows from table1 if there are more than 10 rows, or it will return a single row (not present in the database) otherwise.
4 Comments
dani herrera
only if both tables have the same schema ... asked to OP and waiting for a response ;)
fthiella
@danihp yes, they need to have the same schema, otherwise it won't work... also, it's not clear where those variables come from :) we need some more details
hemant
Hi, the problem statement is I have a table and I want a record from table that satisfy a certain condition, say if there are 10 same record in table then return the 10 records. If no record for that condition then add a row and return 1 record . in var1 I'm taking counts from table and var2 is 10.
fthiella
@hemant do you actually have to insert a row into a table if the condition is not met? i'll update my answer, but i don't think it's possible do insert and select at the same time, but maybe a different solution would be fine anyway
var*are externally allocated variables? Or fetched from table?