Query question

Hi - I have a question about a query I'm struggling with - please redirect me if this is not an appropriate place to ask.

I've been trolling the net for hours trying to find a solution to this, but obviously I am dumb :o)

What I have is two tables, one with projects and another with personnel linked to projects:

PROJECTS
- projectID (pk)

• projectName varchar
• <other data>

LINKED PERSONNEL
- projectID (fk into PROJECTS)

• personName varchar
• role varchar

So, LinkedPersonnel is in a many-to-one relationship to Projects. i.e: a project can have many personnel, all with different roles.

What I want to do is to select data from Projects, but including the person who plays the 'Primary Investigator' role for that project. Baring in mind that:

• I want at least one row per project in the result
• A project might not have a Primary Investigator (in which case the selected role field should be null)
• A project might have more than one Primary Investigator (in which case, we want a seperate result for each primary investigator

I can't restrict using the WHERE clause, since that removes projects which have no PI.

I've tried various forms of subqueries, but to date, with no luck.

If anyone can help, I'd appreciate it. Apologies for being thick =) Received on Sat Jun 02 2007 - 21:50:29 CDT