Search code examples
sqlsql-serverdatabaseinner-join

SQL Server query with multiple INNER JOINs


I need to execute a query that gets everything from the table Incidents based on a CustomerID. The CustomerID comes from the MasterAccounts table, which is not directly related to the Incidents table. There is a SubAccounts table that could be INNER JOIN-ed to relate the two.

Here is what I have so far:

SELECT
    *
FROM
    MasterAccounts AS ma
INNER JOIN 
    SubAccounts AS sa
INNER JOIN 
    Incidents AS i
WHERE
    ma.CustomerID = sa.CustomerID;
    AND sa.AccountID = i.AccountID
    AND IncidentTypeID = 11;

This is failing with

Incorrect syntax near the keyword 'WHERE'.

Any thoughts would be greatly appreciated!


Solution

  • You need to define join conditions with ON after your INNER JOIN, to link the two tables:

    SELECT
        *
    FROM
        MasterAccounts AS ma
    INNER JOIN 
        SubAccounts AS sa ON ma.CustomerID = sa.CustomerID;
    INNER JOIN 
        Incidents AS i ON sa.AccountID = i.AccountID
    WHERE
        IncidentTypeID = 11;
    

    Those columns must exist in the tables involved, and should have the same datatype (because otherwise the JOIN might cause a "hidden" data type conversion which can be costly in terms of performance)