CODEWITHSUNDEEP
sqlsql-server-2008left-join
Adam Levitt
Adam Levitt

Reputation: 10476

SQL LEFT JOIN combined with regular joins

I have the following query that joins a bunch of tables.

I'd like to get every record from the INDUSTRY table that has consolidated_industry_id = 1 regardless of whether or not it matches the other tables. I believe this needs to be done with a LEFT JOIN?

SELECT attr.industry_id        AS option_id,
       attr.industry           AS option_name,
       uj.ft_job_industry_id,
       Avg(CASE
             WHEN s.salary > 0 THEN s.salary
           END)                AS average,
       Count(CASE
               WHEN s.salary > 0 THEN attr.industry
             END)              AS count_non_zero,
       Count(attr.industry_id) AS count_total
FROM   industry attr,
       user_job_ft_job uj,
       salary_ft_job s,
       user_job_ft_job ut,
       [user] u,
       user_education_mba_school mba
WHERE  u.user_id = uj.user_id
       AND u.user_id = ut.user_id
       AND u.user_id = mba.user_id
       AND uj.ft_job_industry_id = attr.industry_id
       AND uj.user_job_ft_job_id = s.user_job_id
       AND u.include_in_student_site_results = 1
       AND u.site_instance_id IN ( 1 )
       AND uj.job_type_id = 1
       AND attr.consolidated_industry_id = 1
       AND mba.mba_graduation_year_id NOT IN ( 8, 9 )
       AND uj.admin_approved = 1
GROUP  BY attr.industry_id,
          attr.industry,
          uj.ft_job_industry_id

This returns only one row, but there are 8 matches in the industry table where consolidated_industry_id = 1.

--- EDIT: The real question here is, how do I combine the LEFT JOIN with the regular joins?

Upvotes: 0

Views: 317

Answers (1)

Guffa
Guffa

Reputation: 700850

Use left join for tables that may miss a corresponding record. Put the conditions for each table in the on clause of the join, not in the where, as that would in effect make them inner joins anyway. Something like:

select
  attr.industry_id AS option_id, attr.industry AS option_name,
  uj.ft_job_industry_id, AVG(CASE WHEN s.salary > 0 THEN s.salary END) AS average, 
  COUNT(CASE WHEN s.salary > 0 THEN attr.industry END) as count_non_zero,
  COUNT(attr.industry_id) as count_total 
from
  industry attr
  left join user_job_ft_job uj on uj.ft_job_industry_id = attr.industry_id and uj.job_type_id = 1 and uj.admin_approved = 1
  left join salary_ft_job s on uj.user_job_ft_job_id = s.user_job_id 
  left join [user] u on u.user_id = uj.user_id and u.include_in_student_site_results = 1 and u.site_instance_id IN (1)
  left join user_job_ft_job ut on u.user_id = ut.user_id
  left join user_education_mba_school mba on u.user_id = mba.user_id and mba.mba_graduation_year_id not in (8, 9)
where
  attr.consolidated_industry_id = 1 
group by
  attr.industry_id, attr.industry, uj.ft_job_industry_id

If you have any tables that you know always have a corresponding record, just use innser join for that.

Upvotes: 2

Related Questions