CODEWITHSUNDEEP
mysqlsqlsubquery
Zerium
Zerium

Reputation: 17333

MySQL - Use a placeholder for the value of a subquery where no rows are returned

This is basically the structure of my query:

SELECT * FROM foo WHERE foo1 = (subquery here)

I want to detect when the subquery doesn't return any rows, and then use another value as a placeholder.

How can I do that?

Upvotes: 3

Views: 3925

Answers (4)

Zerium
Zerium

Reputation: 17333

Got the answer!

SELECT * FROM foo WHERE foo1 = IF((subquery with count parameter as replacement), placeholder, (subquery))

Upvotes: 1

Pranshu Jain
Pranshu Jain

Reputation: 570

you can use left join, it will work : 

SELECT * FROM table AS t1
left join 
table as t2 
on t1.Target = t2.Target
and t2.phase="B"
where t2.target is null OR 
OR t1.date < t2.Date

you can replace your conditions here by changing null ans your conditions here. By doing a left join you are including all rows on the left side of the join, and only matching rows from the right side of the join. Assuming a left side table t1, and right side table t2, in cases where the join condition is not met the value of any column in t2 will be be null. Since the goal in this case is to omit the where clause if our join condition is not met (targets match and the t2 phase value is 'B') we first check to see if the join condition failed, if so we return a row.

Upvotes: 1

Pranshu Jain
Pranshu Jain

Reputation: 570

you can just pass this by a condition above, like count your fetched data using using mysql_num_rwos , and assign that value to a variable and then put it in if else loop for conditions like as : 

$a = mysql_num_rows(query);
if($a == '')
{
$a = your placeholder here;
}
SELECT * FROM foo WHERE foo1 ='".$a."'

something like that.

Upvotes: 0

William Kinaan
William Kinaan

Reputation: 28809

Try your subquery alone , then use this: mysql_num_rows to know how many rows has been affected by your subquery, if the number is 0 then create a query with another place holder

note rowCount() to know the number of rows using PDO

Upvotes: 0

Related Questions