kracklt
asked on
Left Join with condition in joined table
Hi Experts:
I have tables which I am joining like so:
select
site.site_id,
site.site_name,
sum(distinct(test.`nr_of_p art`)) as total_part,
site_type.site_type_name
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.`tes t_end`) <= '31536000'
group by
site.site_id
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.
kracklt :-)
BTW: I am using MySQL 5.1
I have tables which I am joining like so:
select
site.site_id,
site.site_name,
sum(distinct(test.`nr_of_p
site_type.site_type_name
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
group by
site.site_id
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.
kracklt :-)
BTW: I am using MySQL 5.1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect. I did not know thet the left join statement can be extendend :-)
Thanks a lot!
Kracklt