[SOLVED] Advanced: Multiple Left Joins Where Condition

[PatPHP]

Hi Guys

 

SELECT Count(*) AS Amount, cat.Category
FROM tboffence AS ofc 
LEFT JOIN tblcategory AS cat ON ofc.CategoryID = cat.ID 
LEFT JOIN tblmisdemeanourtype mis ON ofc.MisdemeanourTypeID=mis.ID 
LEFT JOIN tbjustification jus ON jus.OffenceID=ofc.ID
WHERE ofc.StudentID=222 AND ofc.OffenceDate >= '2008-06-02' 
AND ofc.OffenceDate <= '2008-08-29' AND jus.Accepted!=1 
GROUP BY cat.Category;

 

If I run the query without the "AND jus.Accepted!=1" it works great and

gives me back all the offences grouped by category how I expected. But

as soon as I run the query with that condition it breaks. It just works when

there is a link between tbjustification and tbstudentpc. So how can I also

get the rows when there is no justification created???

 

I tried following condition as well but didn't work:

(jus.Accepted!=1 OR jus.Accepted=NULL)

 

Thanks

 

 

[Barand]

try moving the condition from the WHERE clause to the JOIN condition

 

SELECT Count(*) AS Amount, cat.Category
FROM tboffence AS ofc 
LEFT JOIN tblcategory AS cat ON ofc.CategoryID = cat.ID 
LEFT JOIN tblmisdemeanourtype mis ON ofc.MisdemeanourTypeID=mis.ID 
LEFT JOIN tbjustification jus ON jus.OffenceID=ofc.ID AND jus.Accepted!=1
WHERE ofc.StudentID=222 AND ofc.OffenceDate >= '2008-06-02' 
AND ofc.OffenceDate <= '2008-08-29' 
GROUP BY cat.Category

[PatPHP]

Hi

 

First of all thank you very much, I didn't know that you can put it into the

JOIN condition. But anyway if I put it into the Join condition the condition

'jus.Accepted!=1' seems to be ignored. I get the same result as I remove it.

Probably because it is left join and it still returns the record for the table

tboffence which is counted. Hmm not easy that one...

 

Do you have another idea?

[lampstax]

Hmm.

 

Very interesting technique.  Can you explain a bit more what exactly moving the condition to the join does ?

 

I tried a simple join with that technique on my table and it came out with unexpected results :

 

SELECT * FROM `articles` AS `a` LEFT JOIN `files` AS `f` ON `a`.`id` = `f`.`article_id` WHERE `f`.`article_id` IS NULL

 

this query returned 123 rows ( which is the number of articles with no images uploaded )

 

SELECT * FROM `articles` AS `a` LEFT JOIN `files` AS `f` ON `a`.`id` = `f`.`article_id` AND `f`.`article_id` IS NULL

 

returned ALL rows in articles table joined with columns from files table but all values in files table was NULL

 

As you can see the only difference is moving the WHERE to the join condition.  However, results were strange.

[PatPHP]

Hi Guys

 

Thx lampstax

 

Actually the code seems to run now if I write in the where condition:

(jus.Accepted!=1 OR jus.Accepted IS NULL)

 

What I wrote before was jus.Accepted=NULL which was wrong syntax.

 

SELECT Count(*) AS Amount, cat.Category
FROM tboffence AS ofc 
LEFT JOIN tblcategory AS cat ON ofc.CategoryID = cat.ID 
LEFT JOIN tblmisdemeanourtype mis ON ofc.MisdemeanourTypeID=mis.ID 
LEFT JOIN tbjustification jus ON jus.OffenceID=ofc.ID
WHERE ofc.StudentID=222 AND ofc.OffenceDate >= '2008-06-02' 
AND ofc.OffenceDate <= '2008-08-29' AND (jus.Accepted!=1 OR jus.Accepted IS Null) 
GROUP BY cat.Category;

[abouchoud]

hello,

 

you can write it like that :

 

SELECT Count(*) AS Amount, cat.Category

FROM tboffence AS ofc

LEFT JOIN (tblcategory AS cat, tblmisdemeanourtype AS mis, tbjustification AS jus)

ON (ofc.CategoryID = cat.ID, ofc.MisdemeanourTypeID=mis.ID, jus.OffenceID=ofc.ID)

WHERE ofc.StudentID=222 AND ofc.OffenceDate >= '2008-06-02'

AND ofc.OffenceDate <= '2008-08-29' AND (jus.Accepted!=1 OR jus.Accepted IS Null)

GROUP BY cat.Category;