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_part`)) 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.`test_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
krackltAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
move the condition of the left joined table to the join condition itself:

select
  site.site_id,
  site.site_name,
  sum(distinct(test.`nr_of_part`)) 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`
        and UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(test.`test_end`) <= '31536000'
   
   
    where site.site_type_id = site_type.site_type_id
           
                                         
                        group by
                          site.site_id
0
 
krackltAuthor Commented:
Hi Angel:

Perfect. I did not know thet the left join statement can be extendend :-)

Thanks a lot!

Kracklt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.