Combining LEFT JOIN with standard joins in SQL query

Data and Databases
1

I’m working on a SQL query that pulls data from multiple tables. My goal is to get all records from the SECTOR table where grouped_sector_id equals 1, even if they don’t match other tables. I think I need to use a LEFT JOIN for this.

Here’s a simplified version of my current query:

SELECT s.sector_id AS choice_id,
       s.sector AS choice_name,
       e.full_time_sector_id,
       AVG(CASE WHEN p.pay > 0 THEN p.pay END) AS mean_pay,
       COUNT(CASE WHEN p.pay > 0 THEN s.sector END) AS non_zero_count,
       COUNT(s.sector_id) AS total_count
FROM sector s
JOIN employee_job e ON s.sector_id = e.full_time_sector_id
JOIN pay p ON e.employee_job_id = p.job_id
JOIN employee m ON e.employee_id = m.employee_id
WHERE s.grouped_sector_id = 1
      AND m.include_in_reports = 1
      AND e.job_category = 'Full-time'
GROUP BY s.sector_id, s.sector, e.full_time_sector_id

This query only returns one row, but there are 8 matching records in the sector table where grouped_sector_id is 1. How can I modify this query to include all these records while keeping the other joins intact?

2

yo SwimmingFish! i think i got what u need. try changing all ur JOINs to LEFT JOINs. that’ll keep all ur sector records. also, tweak ur WHERE clause like this:

WHERE s.grouped_sector_id = 1
AND (m.include_in_reports = 1 OR m.include_in_reports IS NULL)
AND (e.job_category = ‘Full-time’ OR e.job_category IS NULL)

that should do the trick! lemme know if it works for ya

3

hey there! have u tried using LEFT JOIN instead of regular JOIN? that might help u get all the sector records u want. also, don’t forget to adjust ur WHERE clause for NULL values. curious to hear if that works for u! let me know if u need more help or wanna chat about SQL tricks :slight_smile:

4

To solve your issue, you need to modify your query to use LEFT JOINs instead of regular JOINs. This will ensure all records from the SECTOR table are included, even without matches in other tables. Here’s the key change:

FROM sector s
LEFT JOIN employee_job e ON s.sector_id = e.full_time_sector_id
LEFT JOIN pay p ON e.employee_job_id = p.job_id
LEFT JOIN employee m ON e.employee_id = m.employee_id

Also, adjust your WHERE clause to handle NULL values:

WHERE s.grouped_sector_id = 1
AND (m.include_in_reports = 1 OR m.include_in_reports IS NULL)
AND (e.job_category = ‘Full-time’ OR e.job_category IS NULL)

This should return all 8 matching records from the SECTOR table while maintaining the integrity of your other joins and calculations.