How to get result of sub-query even if main query doesn't return any row?

1
votes

This is my 1st Query

select column1, column2, column3 from table1 where id=2 LIMIT 1

This is my 2nd Query

select COUNT(*) from table2 where id=22

This is my 3rd Query

select COUNT(*) from table2 where column1='bla bla bla' AND id=33

I want to merge 2nd and 3rd query in 1st query as sub-queries like this

select column1, column2, column3 (select COUNT(*) from table2 where id=22) as count1, (select COUNT(*) from table2 where column1='bla bla bla' AND id=33) as count2 from table1 where id=2 LIMIT 1

This is working fine if main query gives result based on your where condition. If main query does not return any row then I am also not able to know the count1 and count2. But I want to know the result of 2nd and 3rd sub-query (i.e., count1 and count2) even if main query returns no row.

How can I do that ?

1
mysqlsql

1 Answers

0
votes

Move the subqueries to the from clause and use left join:

select column1, column2, column3, t22.cnt, t33.cnt_blablabla
from (select cnt(*) as cnt from table2 where id = 22) t22 cross join
     (select count(*) as cnt_blablabla from table2 where column1 = 'bla bla bla' and id = 33) t33 left join
     table1 t1
     on t1.id = 2
limit 1