CODEWITHSUNDEEP
sqlsql-server
Russ Toms
Russ Toms

Reputation: 67

MSSQL statement referencing across tables

So I have two tables, disciplinary and employees. Disciplinary has a column that lists an employee ID (an investigator) and an attempt to import new columns that are drawn from the employee table that yield the employee first and last name based on the existing employee ID from the disciplinary table. Below is the SQL I have so far:

    SELECT d.*
    , inv.firstName as investigatorFirstName
    , inv.lastName as investigatorLastName

    FROM det_siu_disciplinary d

    LEFT OUTER JOIN cpso_employees inv ON
    inv.commissionNumber = d.investigatorEmployeeID

    WHERE d.isDelete = 0

This statement successfully adds the joined columns with their new names, but all columns are null. My primary concern is my SQL being flat out wrong, as it's the part of this process that I have least experience with. These statements are part of a much larger query, so if at all possible I'd prefer to not write a new query...adding contingencies would be perfect!

Anyone that assists, thank you in advance :)

Upvotes: 1

Views: 75

Answers (1)

DarrenMB
DarrenMB

Reputation: 2380

the primary key column "CommissionNumber" seems unlikely to me to be the primary key of a table that should contain "EmployeeID" values, in order to join to the foreign key columns of your d table.

Upvotes: 1

Related Questions