Re: Query question

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 3 Jun 2007 21:03:25 +0200
Message-ID: <4663107d$0$7450$426a74cc@news.free.fr>

"Peter Ashford" <peter.m.ashford_at_gmail.com> a écrit dans le message de news: 1180859758.187785.103270_at_z28g2000prd.googlegroups.com... On Jun 3, 5:48 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "Peter Ashford" <peter.m.ashf..._at_gmail.com> a écrit dans le message de news:
> 1180839029.192876.326..._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/que...
>
> Especially paragraph "Outer Joins":http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/que...

Thanks, but as I said, I've read lots of documentation on the net. The reason I'm asking here is that I just don't get it :o)

SQL> select d.dname, e.ename, e.comm
  2 from dept d, emp e
  3 where e.deptno (+) = d.deptno
  4 and e.comm (+) is not null
  5 order by 1,2
  6 /
DNAME ENAME COMM
-------------- ---------- ----------
ACCOUNTING
OPERATIONS
RESEARCH

SALES          ALLEN             300
SALES          MARTIN           1400
SALES          TURNER              0
SALES          WARD              500

7 rows selected.

Regards
Michel Cadot Received on Sun Jun 03 2007 - 14:03:25 CDT