Re: SQL for spreadsheet output - Part 2

andyza_at_webmail.co.za (Luis) wrote in message news:<69476b6f.0407282131.4a2221cf_at_posting.google.com>...
> Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<0psfg05ls7q76ho09515mhu6i9fv1g38m0_at_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 Received on Thu Jul 29 2004 - 12:50:00 CDT