1

My query can be simplified as this:

SELECT a.id, a.name, b.* FROM a LEFT JOIN b ON a.id=b.id

Which b is from group concat select (very dynamic column). Sometimes there are 3 columns in b, sometimes 10 columns depend on filtering.

What I want is something like this:

SELECT a.id, a.name, IFNULL(b.*,0) FROM a LEFT JOIN b ON a.id=b.id

I already looked here and here and the answer is always repeating per column. But I can't apply ifnull per column because I don't know what the columns are. Is there any other approach for replacing null values from left join?

All columns are of the integer type.

1

If I understand correctly, you could have any number of columns from table B? If so, then you will need to dynamically create the SQL statement (by querying the information_schema table) and just wrap each column from B in your ifnull(field, ifnullvalue).

  • It's not from specific table, it's generated table from query with dynamic column based on select. So i need all column selected from join to be replaced by 0 if they retun null value. – Septian Primadewa Jul 21 '15 at 0:56
  • @SeptianPrimadewa the answer does not talk about any base table either. It advises you dynamically build this query, the same way you dynamically build the B derived table/view/query (whatever that is). – ypercubeᵀᴹ Jul 21 '15 at 1:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.