RE: [DUG]: me and my sql's again

[David Brennan]

Ooops, s.skillid it is.   Btw, what database server are you using? Some servers such as SQL Server wouldn't actually allow your original syntax because skillid is ambiguous. Can be annoying at times but in the long run warnings like this probably save us time.   David. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Tracey Maule Sent: Tuesday, 11 March 2003 1:46 PM To: Multiple recipients of list delphi Subject: Re: [DUG]: me and my sql's again ah thank you David (s.skillid)     ----- Original Message ----- From: David Brennan To: Multiple recipients of list delphi Sent: Tuesday, March 11, 2003 12:54 PM Subject: RE: [DUG]: me and my sql's again Tracey,   I would say it is because you aren't specifying which version of skillid you want to sort by. I think your database is picking sa.skillid to sort by. That last row fails the join to sa so sa.skillid is null for that row, hence it gets positioned last.   Change th order by line to:   order by p.skillid   HTH David. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Tracey Maule Sent: Tuesday, 11 March 2003 12:44 PM To: Multiple recipients of list delphi Subject: [DUG]: me and my sql's again 1.  Anyone know anywhere in Tauranga that does an intermediate SQL course of some discription?   2.  Could someone tell me why i receive the following recordset with this query? (ie why is the odd skillid at the last?)   select distinct p.name pname, em.name jname, s.name sname, s.skillid, sa.validto, p.personid, sa.result, sn.timeperiodid, pe.commencedate from person p join personemployment pe on pe.personid = p.personid join employment em on em.employmentid = pe.employmentid join skillneeded sn on sn.employmentid = em.employmentid join skill s on sn.skillid = s.skillid left join skillattempt sa on p.personid = sa.personid and s.skillid = sa.skillid where p.companyid = 1 order by skillid         PNAME            JNAME                    SNAME            SKILLID            VALIDTO        PERSONID        RESULT        TIMEPERIODID    COMMENCEDATE --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hata Gordine ALL ROUNDER HAZARD IDENTIFICATION 2 15/03/2003 88 P 3 4/03/2003 Tim DeYoung SUPERVISOR HAZARD IDENTIFICATION 2 1/04/2003 1 P 3 1/03/2003 Chris Smith ADMINISTRATOR BASIC FIRST AID 3 2/04/2003 184 P 2 1/02/2003 Hata Gordine COURIER BASIC FIRST AID 3 10/04/2003 88 P 2 1/03/2003 Hata Gordine ALL ROUNDER BASIC FIRST AID 3 10/04/2003 88 P 2 4/03/2003 Hata Gordine ALL ROUNDER BASIC FIRST AID 3 10/04/2003 88 P 12 4/03/2003 Tim DeYoung FORKLIFT DRIVER BASIC FIRST AID 3 5/03/2005 1 P 2 1/03/2003 Tim DeYoung SUPERVISOR BASIC FIRST AID 3 5/03/2005 1 P 2 1/03/2003 Tim DeYoung FORKLIFT DRIVER FORKLIFT LICENSE 5 31/03/2003 1 D 1 1/03/2003 Tim DeYoung FORKLIFT DRIVER FORKLIFT LICENSE 5 2/03/2007 1 P 1 1/03/2003 Hata Gordine ALL ROUNDER TYPING 101 6 12/03/2003 88 P 1 4/03/2003 Chris Smith ADMINISTRATOR TYPING 101 6 184 N 1 1/02/2003 Hata Gordine COURIER CLASS A LICENSE 7 4/03/2006 88 P 1 1/03/2003 Hata Gordine ALL ROUNDER CLASS A LICENSE 7 4/03/2006 88 P 1 4/03/2003 Hata Gordine ALL ROUNDER FORKLIFT LICENSE 5 88 11 4/03/2003         Tracey Software Developer / Web Master Logis [EMAIL PROTECTED] (025) 213-1065