SQL Joining of Multiple Tables

SQL newbie here, I am trying to have the table print out the sum of the wages, make, and car model for all the people that own a particular make/model combination. As of right now, the table prints out all the Car Make and Model values, but the SumWage column is all NULL. The SumWage should return the total sum of all the wages of people who have the Make/Model combination.(My select statement is all the way at the bottom of the code)? All advice is much appreciated!

IF OBJECT_ID ('Person', 'U') IS NULL 

BEGIN

    CREATE TABLE Person (
        ID INT NOT NULL PRIMARY KEY,
        Name VARCHAR(32),
        BirthDate DATETIME 
        );

    INSERT INTO Person
        ( ID, Name, BirthDate )
    VALUES
        (311113, 'Dan Lu', '01-22-33'), 
        (123456, 'Seven Durant', '07-22-94'), 
        (100100, 'Choochootrain Lu', '12-17-56'),
        (106542, 'Spider Ru', '07-22-36'),
        (101010, 'Ru Ru', '04-30-84');
END

IF OBJECT_ID ('Job', 'U') IS NULL 

BEGIN

    CREATE TABLE Job (
        ID INT NOT NULL PRIMARY KEY,
        Company VARCHAR(64),
        Wage FLOAT,
        PersonID INT FOREIGN KEY REFERENCES Person(ID)
        );

    INSERT INTO Job
        ( ID, Company, Wage, PersonID )
    VALUES
        (01, 'Space Pizza Space', 1000.00, 311113), 
        (02, 'Bread', 46.44, 101010), 
        (03, 'Delivery Service', 400.99, 100100), 
        (04, 'Nike', 999900.01, 106542), 
        (05, 'Old McDonald', 6500210.77, 123456);
END


IF OBJECT_ID ('Car', 'U') IS NULL 

BEGIN

    CREATE TABLE Car (
        ID INT NOT NULL PRIMARY KEY,
        Make VARCHAR(32),
        Model VARCHAR(32)       
        );

    INSERT INTO Car
        ( ID, Make, Model )
    VALUES
        (1234, 'Lexus', 'SE'), 
        (4444, 'Tesla', 'X'), 
        (5007, 'Chevy', 'Cobalt'),
        (7771, 'Ford', 'Runner'),
        (6459, 'Toyota', 'Camry');
END

IF OBJECT_ID ('PersonCar', 'U') IS NULL 

BEGIN

    CREATE TABLE PersonCar (
        ID INT,
        PersonID INT FOREIGN KEY REFERENCES Person(ID),
        CarID INT       
        );

    INSERT INTO PersonCar
        ( ID, PersonID, CarID )
    VALUES
        (1, 311113, 1234), 
        (2, 123456, 4444), 
        (3, 100100, 5007),
        (4, 106542, 7771),
        (5, 101010, 6459);
END

SELECT SUM(Wage) AS SumWages, Car.Make, Car.Model
FROM Person
    INNER JOIN PersonCar 
        ON PersonCar.ID = Person.ID
    INNER JOIN Job 
        ON Job.PersonID = PersonCar.ID 
    Right JOIN Car
        ON Car.ID = Person.ID
GROUP BY Wage, Make, Model;

Answer

I think that you want:

SELECT SUM(j.Wage) AS SumWages, c.Make, c.Model
FROM Car c
LEFT JOIN PersonCar pc ON pc.CarID = c.ID
LEFT JOIN Job j ON j.PersonID = pc.PersonID
GROUP BY c.Make, c.Model;

This selects all Cars, and follows the relationships towards the Job table, that contains the Wage of each car owner. Data is aggregated by car model and model, and the corresponding wages are sumed.

When a given make/model tuple has no car owner, the sum comes out as null (if you want to just remove these rows from the resultset, use INNER JOINs instead of LEFT JOINs).

Note that you don’t need to bring the Person table to get this resultset.

Advertisement

Answer