Join Trouble

[Sir Phoenix]

I have two entities. A truck and some truck parts.

A truck can have many parts.

I have a query that tells me info about the truck, as well as how much total time is left on the truck's parts. To do this, I take the sum of the 'time left' on each truck part, then group it by truck.

However, I have two issues.

I need to see all trucks, even if they don't have any parts. An inner join excludes these trucks.

If I try to solve the above with a left join (show all trucks, and show all matching parts), I get an error, I assume from trying to SUM a field that doesn't exist. (In the case of trucks without parts)

How do I solve this?

 

[CT Witter]

How are you tables setup? What data are you using? An example of your query would be helpful.

CT

 

[Sir Phoenix]

SELECT [Truck].truckID, [Truck].[Order#], [Truck].Priority, Sum(TBA.Hours) AS SumOfHours
FROM [Truck] LEFT JOIN TBA ON [Truck].truckID = TBA.truckID
GROUP BY [Truck].truckID, [Truck].[Order#], [Truck].Priority
ORDER BY [Truck].Priority;

Or switch "LEFT JOIN" with "INNER JOIN" to get the first version.

If I try to run this, I get "No current Record" (left join)

or

Some trucks will be omitted, the ones without TBAs. (inner join)

 

[CT Witter]

Ok, I have table Truck:

----------------------------------------------------------------
| truckID | Order# | Priority |
----------------------------------------------------------------
| 1 | 1 | 1 |
----------------------------------------------------------------
| 2 | 2 | 2 |
----------------------------------------------------------------
| 3 | 3 | 3 |
----------------------------------------------------------------

table TBA

-------------------------------------------
| TruckID | Hours |
-------------------------------------------
| 1 | 10 |
-------------------------------------------
| 2 | 5 |
-------------------------------------------

Query:

SELECT Truck.truckID, Truck.[Order#], Truck.Priority, Sum(TBA.Hours) AS SumOfHours
FROM Truck LEFT JOIN TBA ON Truck.truckID = TBA.TruckID
GROUP BY Truck.truckID, Truck.[Order#], Truck.Priority
ORDER BY Truck.Priority;

Yields

-------------------------------------------------------------------------------------
| truckID | Order# | Priority | SumOfHours |
-------------------------------------------------------------------------------------
| 1 | 1 | 1 | 10 |
-------------------------------------------------------------------------------------
| 2 | 2 | 2 | 5 |
-------------------------------------------------------------------------------------
| 3 | 3 | 3 | |

Everything looks ok to me.

 

[Sir Phoenix]

Hrm. Looking at this query again, I see that it didn't get it's data from the Truck table, but from a query that selects the Truck table. I rewrote it only using tables and it seems to work now. Thanks!