Continuing from my post a few days ago, I want to produce a list of
people and the qualificatons they have obtained. I have two tables in
a Oracle 9i database:

TblPerson { PersonId(PK), PersonName, PersonSurname, PersonTel }
TblEducation { QualificationId, PersonId(FK), QualificationName,
DateObtained }

One person can have multiple qualifications. In this instance it
doesn't matter that a particular qualification can be obtained by more
than one person and I don't need to cater for that possibility.

As per my original post I want to generate an excel spreadsheet of
Persons
and the qualifications they have obtained.

I want the spreadsheet to be laid out as follows if the person only
has one qualification:

PersonId | PersonName | PersonSurname| PersonTel | QualificationId |
QualificationName | DateObtained

If the person has two qualifications it will look like this:
PersonId | PersonName | PersonSurname| PersonTel | QualificationId |
QualificationName | DateObtained | QualificationId | QualificationName
| DateObtained

And for three qualifications:
PersonId | PersonName | PersonSurname| PersonTel | QualificationId |
QualificationName | DateObtained | QualificationId | QualificationName
| DateObtained | QualificationId | QualificationName | DateObtained

And so on for more qualifications.

How do I get this result?

On 28 Jul 2004 09:40:20 -0700, andyza@webmail.co.za (Luis) wrote:

>How do I get this result?

Show what you've got so far and where you are going wrong. As this is
a volunteer group we don't necessarily do your work for you.


--
Sybrand Bakker, Senior Oracle DBA

Sybrand Bakker <sybrandb@hccnet.nl> wrote in message news:<0psfg05ls7q76ho09515mhu6i9fv1g38m0@4ax.com>. ..
> Show what you've got so far and where you are going wrong.

Using the following structure:
TblPerson { PersonId(PK), PersonName, PersonSurname, PersonTel }
TblEducation { QualificationId, PersonId(FK), QualificationName,
DateObtained }

This query:
SELECT PER.PersonId, EDU.PersonId, PER.PersonName, PER.PersonTel,
EDU.QualificationId, EDU.QualificationName, EDU.DateObtained
FROM TblPerson PER, TblEducation EDU
WHERE ((PER.PersonId = EDU.PersonId(+))

Gives me this result:
19612121,19612121,Peter Smith,234-444,Degree888,BComm,1989/05/24
19612121,19612121,Peter Smith,234-444,Diploma555,Marketing,1995/06/23
19881005,19881005,Mary Jane,587-3548,Diploma246,Accounting,1998/10/10
19500101,19500101,Jon Brown,222-3333,Degree195,Phd,1990/01/01

Peter Smith has 2 qualifications while the others have 1.
I'm stuck at the part where I join the two Peter Smith records so that
I get:

19612121,19600505,Peter
Smith,234-444,Degree888,BComm,1989/05/24,Diploma555,Marketing,1995/06/23

19881005,19881005,Mary Jane,587-3548,Diploma246,Accounting,1998/10/10

19500101,19500101,Jon Brown,222-3333,Degree195,Phd,1990/01/01

(example may wrap)

andyza@webmail.co.za (Luis) wrote in message news:<69476b6f.0407282131.4a2221cf@posting.google. com>...
> Sybrand Bakker <sybrandb@hccnet.nl> wrote in message news:<0psfg05ls7q76ho09515mhu6i9fv1g38m0@4ax.com>. ..
> > Show what you've got so far and where you are going wrong.
>
> Using the following structure:
> TblPerson { PersonId(PK), PersonName, PersonSurname, PersonTel }
> TblEducation { QualificationId, PersonId(FK), QualificationName,
> DateObtained }
>
> This query:
> SELECT PER.PersonId, EDU.PersonId, PER.PersonName, PER.PersonTel,
> EDU.QualificationId, EDU.QualificationName, EDU.DateObtained
> FROM TblPerson PER, TblEducation EDU
> WHERE ((PER.PersonId = EDU.PersonId(+))
>
> Gives me this result:
> 19612121,19612121,Peter Smith,234-444,Degree888,BComm,1989/05/24
> 19612121,19612121,Peter Smith,234-444,Diploma555,Marketing,1995/06/23
> 19881005,19881005,Mary Jane,587-3548,Diploma246,Accounting,1998/10/10
> 19500101,19500101,Jon Brown,222-3333,Degree195,Phd,1990/01/01
>
> Peter Smith has 2 qualifications while the others have 1.
> I'm stuck at the part where I join the two Peter Smith records so that
> I get:
>
> 19612121,19600505,Peter
> Smith,234-444,Degree888,BComm,1989/05/24,Diploma555,Marketing,1995/06/23
>
> 19881005,19881005,Mary Jane,587-3548,Diploma246,Accounting,1998/10/10
>
> 19500101,19500101,Jon Brown,222-3333,Degree195,Phd,1990/01/01
>
> (example may wrap)

outer join to the education table more than once, where the degreeobtained differs.

Ed

On 29 Jul 2004 10:50:00 -0700, ed.prochak@magicinterface.com (Ed
prochak) wrote:
>
>outer join to the education table more than once, where the degreeobtained differs.
>
>Ed

Thanks. Can you explain how?