Left Join with condition in joined table
Posted on 2007-10-17
I have tables which I am joining like so:
sum(distinct(test.`nr_of_part`)) as total_part,
from site_type, site
left outer join site_cases
on site.`site_id` = site_cases.`site_id`
left outer join test
on site.`site_id` = test.`site_id`
where site.site_type_id = site_type.site_type_id
and UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(test.`test_end`) <= '31536000'
This statement works as expected (I shortened it a bit...). Except for one thing which I am trying to get around:
When I leave out the "UNIX_TIMESTAMP..." condition, I get all sites regardless of if they have any tests done or not. If I put in the condition I am not getting sites which have no tests anymore...
I underrstand that this makes sense in such a way that if I am requesting only sites which have tests in a certain timeframe, that I won't get those who do not have studies.
Here is my question: Is there a way to get all Sites (regardless if they habe done a test or not) whilst only using the timeframe restriction for calculating the sum()?
Sorry if this is a bit confusing. Let me know if you have any questions in regards to this.
BTW: I am using MySQL 5.1