CODEWITHSUNDEEP
sqlt-sql
mrtsherman
mrtsherman

Reputation: 39872

How to join in an SQL query across four tables?

I am not very good with SQL so I will post the tables I am working with and what I have tried.

Table 1

Installation
   id

Table 2 links to 1

Project
   id
   installationid
   name

Table 3 links to 2

Assignment
   id
   projectid

Table 4 links to 3

AssignmentException
   id
   assignmentid
   type

I am trying to find Project.name where AssignmentException.type > 0 for Installation.id = 12345. The most experience I have is with simple inner joins. The following is my nonworking attempt that fails to take into account Installationid. A short explanation of how your code works would also be greatly appreciated.

select * from ( 
    Assignment INNER JOIN AssignmentException ON Assignment.id = AssignmentException.assignmentID )
    INNER JOIN Project ON Assignment.projectid = Project.id
    WHERE AssignmentException.type > 0

Upvotes: 1

Views: 121

Answers (5)

Jonathan Leffler
Jonathan Leffler

Reputation: 753485

SELECT p.name
  FROM AssignmentException AS e
  JOIN Assignment          AS a ON a.id = e.assignmentID
  JOIN Project             AS p ON p.id = a.projectid
 WHERE e.type > 0
   AND p.installationID = 12345

You don't need the Installation table as there is no extra data in it - the InstallationID in Project is sufficient to identify the installation. This is assuming that there is a PK/FK referential constraint between Installation and Project, and that it is enforced by the DBMS

Upvotes: 1

Matt Grande
Matt Grande

Reputation: 12157

I think you're looking for something like this:

SELECT p.Name
FROM Project p
INNER JOIN Assignment a ON a.projectid = p.id
INNER JOIN AssignmentException ae ON ae.assignmentid = a.id
INNER JOIN Installation i ON i.id = p.installationid
WHERE ae.type > 0 AND i.id = 12345

Explanation:

  • You needed the projects name, so that's where I started (first two lines).
  • You said you needed the exception type, so I JOINed Assignment and AssignmentException, and added WHERE ae.type > 0
  • You said you needed Installation ID 12345, so I joined that table and changed the WHERE

Hope this helps.

Upvotes: 1

Justin Pihony
Justin Pihony

Reputation: 67065

SELECT P.Name 
FROM Project AS P
--Using EXISTS as you do not need to join unless it is in your output
--More often, this results in better performance
WHERE
    --This pulls in only those projects with Installation ID = 12345 
    P.InstallationId = 12345
    --This pulls in only projects with Assignments 
    --that have Assignment Exceptions of Type > 0
    AND EXISTS
    (
        SELECT 1 
        FROM Assignment AS A
            JOIN AssignmentException AS AE ON A.ID = AE.ID
        WHERE A.ProjectId = P.Id AND AE.Type > 0
    )

Upvotes: 0

Diego
Diego

Reputation: 36126

select p.Name
from Project P
join Assignment A on A.projectid = P.id
join AssignmentException AE on AE.id=A.id
where AE.AssignmentException>0 and P.installationid = 12345

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147224

How about this:

SELECT p.name
FROM AssignmentException ex
    JOIN Assignment a ON ex.id = a.AssignmentId
    JOIN Project p ON a.ProjectId = p.Id
WHERE ex.type > 0
    AND p.InstallationId = 12345

Upvotes: 2

Related Questions