Problem With Join

[shadowfoxmi]

Hello,
I have a query with two joins on three tables. Here's the scenario.

First table - >Projects
Second table - >[Team Members]
Third Table - >Roles
and I have the join lik this
projects join [team members] join roles
(of course i have the correct syntax with the field names n stuff in my query)

Let's say I'm trying to print
Project Name, Team Member Name, Role Name
it works fine.
Now i hav a filter saying Role Name="Product Manager"
That works fine too. The problem is since it is a equality condition, it is listing only the Project Names with a "Product Manager" in the team. But I want to be able to select all the projects listing the "Product Manager" and in case a team didn't have a "Product Manager", have null returned for Role Name and Team Member Name.
I have tried all join options (left, right, full) ; nothing works because i'm trying to filter on "Product Manager".
Any help on this would be greately appreciated.

Thanks for your time.

[Barand]

I can't test this as I don't have access to SQL Server at home. Try
[code]
SELECT p.name, t.name, r.name
FROM Projects p
LEFT JOIN ([team members] t
INNER JOIN Roles r ON t.roleID = r.roleID AND r.name = 'Product Manager')
ON p.projectID = t.projectID[/code]