Query help

I need help with this query.

My query is a lot more complicated than this but I have distilled it down to this in an example. Since this query will involve many tables and many rows I need it to be as efficient as possible.

My query is retrieving "car", each of these "car"s can have N "features", each "feature" can have 1 "car". I need to get a COUNT on the "features".

Here is what my query looks like so far:

SELECT Car_ID, Car_Name, Manufacturer_ID
FROM [Car], [Manufacturer]
WHERE Car_ManID = Manufacturer_ID

I need to add in something like this:

SELECT Car_ID, Car_Name, Manufacturer_ID, Count(Features) as FeatureCount
FROM [Cars], [Manufacturer], [Feature]
WHERE Car_ManID = Manufacturer_ID AND Feature_CarID = CarID AND CarID = '4'

Now, I know this won't work but you get the point.

Basically, I want to have results like this

Car_ID Car_Name Manufactuere_ID FeatureCount
12 Escort Ford 12
34 Tahoe Chevy 0

Then query must account for feature counts of zero, so I assume some sort of outer join will be needed?add a group by

SELECT Car_ID, Car_Name, Manufacturer_ID, Count(Features) as FeatureCount
FROM [Cars], [Manufacturer], [Feature]
WHERE Car_ManID = Manufacturer_ID AND Feature_CarID = CarID AND CarID = '4'
group by Car_ID, Car_Name, Manufacturer_ID

hth|||Thanks for the reply, I actually just found out about MS SQL derived tables. That works perfect!