Problem join tables result

Transact-SQL
1

Hi All.
I have 3 tables with such structure:

  1. Brand (BrandID, Brand)

  2. DeviceType (DeviceTypeID, BrandID,DeviceType)

  3. Model (ModelID, BrandID, DeviceTypeID, Model)
    When I join Brand and DeviceType tables like:

    SELECT t.BrandID, t.DeviceTypeID, b.Brand, t.DeviceType
    FROM Brand b
    LEFT JOIN DeviceType t
    ON b.BrandID = t.BrandID

The result looks fine. But when I try to join Model table
SELECT t.BrandID, t.DeviceTypeID, b.Brand, t.DeviceType, Model
FROM Brand b
LEFT JOIN DeviceType t
ON b.BrandID = t.BrandID
LEFT JOIN Model m
ON m.DeviceTypeID=t.DeviceTypeID

Result looks mess. How to the problem?
Thanks

2

Please provide sample data with proper ddl and dml

3

You need to include BrandID in the join to the Model table:

LEFT JOIN Model m
ON m.DeviceTypeID=t.DeviceTypeID AND m.BrandID = b.BrandID