SQL statement for evaluating multiple related tables

Question

I have a Projects table, which lists the client info. Then I have four related jobs tables, for jobs within that project - Roofing, Siding, Gutters and Misc. The four tables have a projectID field to link them to the Projects table, and they all have a 'status' field. A project can have any combination of jobs.

I want to be able to select only the projects where all the job status fields are marked "completed."

There's probably a simple way to do this, but my brain overheats sometimes with SQL.

Answer 1

select p.* 
from project as p
where not exists (select 1 from roofing where projectId = p.projectId and status <> 'completed')
    and not exists (select 1 from siding where projectId = p.projectId and status <> 'completed')
    and not exists (select 1 from gutters where projectId = p.projectId and status <> 'completed')
    and not exists (select 1 from misc where projectId = p.projectId and status <> 'completed')

Answer 2

maybe...:

SELECT * FROM Projects
LEFT JOIN Roofing ON (projectID)
LEFT JOIN Siding ON (projectID)
LEFT JOIN Gutters ON (projectID)
LEFT JOIN Misc ON (projectID)
WHERE (Roofing.status IS NULL OR Roofing.status="completed")
AND (Siding.status IS NULL OR Siding.status="completed")
AND (Gutters.status IS NULL OR Gutters.status="completed")
AND (Misc.status IS NULL OR Misc.status="completed")

Answer 3

SELECT *
FROM Projects
  --Step 3: filter the projects by the results from Step2
WHERE ProjectID not in
(
  SELECT ProjectID
  FROM
  (
    --Step 1: gather all the jobs into one bucket
    SELECT ProjectID, Status
    FROM Roofing
      UNION ALL
    SELECT ProjectID, Status
    FROM Siding
      UNION ALL
    SELECT ProjectID, Status
    FROM Gutters
      UNION ALL
    SELECT ProjectID, Status
    FROM Misc
  ) as Jobs
  --Step 2: find incomplete project IDs
  GROUP BY Jobs.ProjectID
  HAVING MIN(Jobs.Status) != 'COMPLETED'
    OR MAX(Jobs.Status) != 'COMPLETED'
)

Answer 4

Alex's approach will get you all the information on one line (if there are no multiple records in the child tables), but if you need it one separtes lines try a union all statement. Just make sure you use the same columns in each union. If you have data in one or more tables that the other tables don't have you would then use null as the value for that column inthe union.

SELECT p1.projectid,'roofing' as JobType FROM Projects p1
JOIN Roofing r ON p1.projectID = r.projectID
union all
SELECT p1.projectid,'gutters' as JobType FROM Projects p1
JOIN gutters g ON p1.projectID = g.projectID
union all
SELECT p1.projectid,'siding' as JobType FROM Projects p1
JOIN Siding s ON p1.projectID = s.projectID
union all
SELECT p1.projectid,'misc' as JobType FROM Projects p1
JOIN Misc m ON p1.projectID = m.projectID

Answer 5

Create a view that returns distinct ProjectIds for "all jobs completed" and join that to the Projects table. That way you only have to update the view if the criteria for "all jobs completed" changes, e.g. if a new job is added. The SQL statement for the view can be constructed several ways as shown in the other replies.