Jump to content


Photo

Problem With Join


  • Please log in to reply
1 reply to this topic

#1 shadowfoxmi

shadowfoxmi
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 29 June 2006 - 03:01 PM

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.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,022 posts

Posted 01 July 2006 - 07:04 PM

I can't test this as I don't have access to SQL Server at home. Try
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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users