Re: Query question

"Peter Ashford" <peter.m.ashford_at_gmail.com> a écrit dans le message de news: 1180839029.192876.326340_at_j4g2000prf.googlegroups.com...
| 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 =)
|

"SQL Reference", Chapter 9 "SQL Queries and Subqueries", section "Joins": http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#i2054012

Especially paragraph "Outer Joins":
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#i2054062

Regards
Michel Cadot Received on Sun Jun 03 2007 - 00:48:55 CDT