Jump to content

[SOLVED] Advanced: Multiple Left Joins Where Condition


PatPHP

Recommended Posts

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

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

  • 3 weeks later...

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;

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.