LEFT JOIN issue with SQL_WHERE clause

[Denis_Sineiko]

I have an issue with displaying JOINED tables together.

This is what I have:

And this is what I would like to have:

The difference between these two pictures is that in the first one I’m using SQL_WHERE in the JOIN and in the second one - not

Does anyone know how to display data the same as in the second picture BUT with sql_where in the join?

[Quinn_Wildman]

How about checking for null in Gross Value and making it be zero instead of null when null encountered? Example:

if(is_null(gross_value),0,gross_value)

[Denis_Sineiko]

@Quinn_Wildman The problem is not only with null to 0, because of the WHERE clause, second column is truncated and display the value only if first column is populated

[zckymc]

It’s be helpful to know a bit more context around the problem e.g. what you’ve defined as the join ON and WHERE clauses.

To display 0 instead of null, try SELECT COALESCE(value,0)

I imagine there’s something in your WHERE clause that’s preventing results from table 2 displaying.

[Denis_Sineiko]

@zckymc Query will be something like this:

SELECT
	MAIN.DATE,
	SUM(X.Gross),
  SUM(Y.Gross)
FROM `MAIN_TABLE` AS MAIN
LEFT JOIN `TABLE1` AS X ON MAIN.Date = X.Date
LEFT JOIN `TABLE2` AS Y ON MAIN.Date = Y.Date

WHERE (X.IsVoided is false) AND (Y.IsVoided is false) 
GROUP BY 1
ORDER BY 1 DESC
LIMIT 500

[zckymc]

Try
WHERE (X.IsVoided is false OR X.IsVoided is null) AND (Y.IsVoided is false OR Y.IsVoided is null)

The problem is that you’re specifying that both tables need to be false, but if one of them is null, then no record will be returned.

[Denis_Sineiko]

Thank you very much )))

[aamax]

I would probably lean more towards using a coalsesce approach…

SELECT
	MAIN.DATE,
	SUM(COALESCE(X.Gross, 0)),
  SUM(COALESCE(Y.Gross, 0))
FROM `MAIN_TABLE` AS MAIN
LEFT JOIN `TABLE1` AS X ON MAIN.Date = X.Date
LEFT JOIN `TABLE2` AS Y ON MAIN.Date = Y.Date

WHERE (X.IsVoided is false) AND (Y.IsVoided is false) 
GROUP BY 1
ORDER BY 1 DESC
LIMIT 500