Re: [DUG]: sql... now i really need help

Tracey Maule Thu, 13 Feb 2003 16:29:35 -0800

The result set im after is this:

JOB(employment.name)         SKILL REQUIRED(skill.name)
-------------------------------------------------------------------------------------------------------------------

SUPERVISORS
                                                 HAZ SUB
                                                 FARTING
                                                 LONG TIME
                                                 GHOSTING
                                                 FORKLIFT LICENCE
                                                 FIRST AID CERT
FORKLIFT DRIVER

                                                 HAZ SUB
                                                 FIRST AID CERT

OFFICE BITCH

FARTING

GHOSTING

(that is for now anyway - eventually i want to have a "current" shown if this person has the skill, and "not current" if they do not have it - but one hurdle at a time.)

er.. data dictionary? I think this is the best IBAdmin can do (of course please correct me if I'm wrong)

/***********************************/
/* Tables */
/***********************************/

/* Table: COMPANY */
CREATE TABLE COMPANY (
       NAME VARCHAR(20) NOT NULL,
       DIRECTORY VARCHAR(65),
       COMPANYID INTEGER NOT NULL,
       EMAIL VARCHAR(50),
       SAFETYCONTACT VARCHAR(50),
       SAFETYPHONE VARCHAR(20),
       MANAGERNAME VARCHAR(50),
       MANAGERPHONE VARCHAR(20),
       LEVEL1GROUPID INTEGER NOT NULL,
      CONSTRAINT PK_COMPANY PRIMARY KEY (COMPANYID)
);

/* Table: EMPLOYMENT */
CREATE TABLE EMPLOYMENT (
       EMPLOYMENTID INTEGER NOT NULL,
       NAME VARCHAR(100) NOT NULL,
       GROUPID INTEGER,
       COMPANYID INTEGER,
      CONSTRAINT EMPLOYMENT_PK PRIMARY KEY (EMPLOYMENTID)
);

/* Table: PERSON */
CREATE TABLE PERSON (
       PERSONID INTEGER NOT NULL,
       NAME VARCHAR(20) NOT NULL,
       PERSONTYPEID INTEGER NOT NULL,
       EMAIL VARCHAR(50),
       PHONE VARCHAR(20),
       NOTES BLOB SUB_TYPE 0,
       ISACTIVE INTEGER,
       COMPANYID INTEGER NOT NULL,
       STAFFCODE NUMERIC(15,2),
       SICKDAYHOURS INTEGER,
      CONSTRAINT PK_PERSON PRIMARY KEY (PERSONID)
);

/* Table: PERSONEMPLOYMENT */
CREATE TABLE PERSONEMPLOYMENT (
       PERSONEMPLOYMENTID INTEGER NOT NULL,
       PERSONID INTEGER NOT NULL,
       EMPLOYMENTID INTEGER NOT NULL,
       COMMENCEDATE DATE NOT NULL,
      CONSTRAINT PERSONEMPLOYMENT_PK PRIMARY KEY (PERSONEMPLOYMENTID)
);

/* Table: PROVIDER */
CREATE TABLE PROVIDER (
       PROVIDERID INTEGER NOT NULL,
       NAME VARCHAR(100) NOT NULL,
       COMPANYID INTEGER NOT NULL,
      CONSTRAINT PROVIDER_PK PRIMARY KEY (PROVIDERID)
);

/* Table: SKILL */
CREATE TABLE SKILL (
       SKILLID INTEGER NOT NULL,
       NAME VARCHAR(100) NOT NULL,
       TIMEVALID INTEGER NOT NULL,          /*this field is to hold the amount of weeks a skill is valid for*/
       COMPANYID INTEGER,
       GROUPID INTEGER,
      CONSTRAINT SKILL_PK PRIMARY KEY (SKILLID)
);

/* Table: SKILLATTEMPT */
CREATE TABLE SKILLATTEMPT (
       SKILLATTEMPTID INTEGER NOT NULL,
       SKILLID INTEGER NOT NULL,
       PERSONID INTEGER NOT NULL,
       ATTEMPTDATE DATE NOT NULL,
       PROVIDERID INTEGER NOT NULL,
       RESULT VARCHAR(1) NOT NULL,          /*P = PASSED
F = NOT PASSED
D = DISQUALIFIED (IN THE CASE OF DRIVERS LICENSE)*/
       REF VARCHAR(50),          /*SUCH AS DRIVERS LICENCE NUMBER ETC*/
       COMMENTS VARCHAR(1000),
       VALIDTO DATE,
      CONSTRAINT SKILLATTEMPT_PK PRIMARY KEY (SKILLATTEMPTID)
);

/* Table: SKILLNEEDED */
CREATE TABLE SKILLNEEDED (
       SKILLNEEDEDID INTEGER NOT NULL,
       EMPLOYMENTID INTEGER NOT NULL,
       SKILLID INTEGER NOT NULL,
       DATEBY DATE,
       TIMEPERIODID INTEGER          /*NUMBER OF WEEKS THIS SKILL MUST BE OBTAINED IN (FROM COMMENCE DATE)*/,
      CONSTRAINT SKILLNEEDED_PK PRIMARY KEY (SKILLNEEDEDID)
);

/* Table: TIMEPERIOD */
CREATE TABLE TIMEPERIOD (
       TIMEPERIODID INTEGER NOT NULL,
       WEEKS INTEGER NOT NULL,
      CONSTRAINT TIMEPERIOD_PK PRIMARY KEY (TIMEPERIODID)
);

----- Original Message -----

From: Donovan J. Edye

To: Multiple recipients of list delphi

Sent: Friday, February 14, 2003 12:17 PM

Subject: Re: [DUG]: sql... now i really need help

T,

What is the result set you are trying to return? Perhaps nested queries are not what you need. Let us have basic data dictionary and what result you want.

At 11:58 14/02/2003 +1300, you wrote:

oh dear, no answers... not surprised

now instead of only returning the one same record every loop through, it is showing ALL possible records even if they arent related:
eg

JOB                                            SKILL REQUIRED
-------------------------------------------------------------------------------------------------------------------

SUPERVISORS
                                                 HAZ SUB
                                                 FARTING
                                                 LONG TIME
                                                 GHOSTING
                                                 FORKLIFT LICENCE
                                                 FIRST AID CERT
FORKLIFT DRIVER

                                                 HAZ SUB
                                                 FARTING
                                                 LONG TIME
                                                 GHOSTING
                                                 FORKLIFT LICENCE
                                                 FIRST AID CERT

could someone please look at my dodgy code and tell me why this is not working?? this should be reading:

JOB                                            SKILL REQUIRED
-------------------------------------------------------------------------------------------------------------------

SUPERVISORS
                                                 HAZ SUB
                                                 FARTING
                                                 LONG TIME
                                                 GHOSTING
                                                 FORKLIFT LICENCE
                                                 FIRST AID CERT
FORKLIFT DRIVER

                                                 HAZ SUB
                                                 FIRST AID CERT

and heres my suspect code:

begin
            with IBQuery2 do
            begin
                Close;
                sQL.Clear;
                SQL.Add('select s.name, s.skillid');
                SQL.Add('from skill s');
                SQL.Add('join skillneeded sn on sn.skillid = s.skillid');
                SQL.Add('where sn.employmentid = :employmentid');
                Prepare;
            end;

            with IBQuery1 do
            begin
                Close;
                SQL.Clear;
                SQL.Add('Select name from person where personid = :personid');
                ParamByName('PersonID').AsString :=
                    Request.QueryFields.Values['person'];
                Open;
                sPerson := FieldByName('Name').AsString;
                Str.Add('<tr><th><h3>Name</h3></th><th><h3>Job</h3></th><th><h3>Skill Required</h3></th><th><h3></h3></th></tr>');
                Str.Add('<tr><td colspan=4><b>' + sPerson + '</b></td></tr>');
                Close;
                SQl.Clear;
                SQL.Add('Select e.name, e.employmentid');
                SQL.Add('from employment e');
                SQL.Add('join personemployment pe on pe.employmentid = e.employmentid');
                SQL.Add('where pe.personid = :PersonID');
                ParamByName('PersonID').AsString :=
                    Request.QueryFields.Values['person'];
                open;
                First;
                while not eof do
                begin
                    sEmploymentID := FieldByName('EmploymentID').AsString;
                    Str.Add('<tr>');
                    Str.Add('<td></td><td><font size=3>' +
                        FieldByName('Name').AsString +
                        '</font></td><td></td><td></td></tr>');
                    IBQuery2.ParamByName('EmploymentID').AsString :=
                        sEmploymentID;
                    IBQuery2.Open;
                    IBQuery2.First;
                    while (not IBQuery2.Eof) do
                    begin
                        Str.Add('<tr><td></td><td></td><td><font size=2>');
                        Str.Add(IBQuery2.FieldByName('Name').AsString);
                        Str.Add('</font></td><td></td></tr>');
                        IBQuery2.Next;
                    end;

                    next;
                end;

            end;
        end


Oh, and important point... WHEN I RUN THE QUERY IN IBADMIN WITH THE EMPLOYMENTID HARD-ENTERED, IT RETURNS EXPECTED RECORDS. I HAVE EVEN PRINTED OUT THE EMPLOYMENTID TO THE REPORT AND IT IS THE CORRECT VALUE THROUGH THE LOOPS.

probably something stupid.....

----- Original Message -----
From: Tracey Maule
To: Multiple recipients of list delphi
Sent: Thursday, February 13, 2003 1:59 PM
Subject: [DUG]: sql... could someone tell me why please??

Hi

I was having a problem with nested queries.. where the first one was

               Select distinct e.name, e.employmentid
               from employment e
               join personemployment pe on pe.employmentid = e.employmentid
               where pe.personid = :PersonID
                ParamByName('PersonID').AsString :=
                    Request.QueryFields.Values['person'];

then i did this:
while not eof do
   //display e.name
query2:
                select s.name
                from skill s
                join skillneeded sn on sn.skillid = s.skillid
                where sn.employmentid = :employmentid
    query2.open;
    while not query2.eof do
        show s.name

this was showing some weird results.. ie the second query was returning only one record, and it was repeated under every employment name (even tho it had no relation to the query1 record)

I banged my head for ages, then on a whim removed the distinct from my first query, now all is well.

Why????????????????????

Tracey
Software Developer / Web Master
Logis
[EMAIL PROTECTED]
(025) 213-1065

-- Donovan
----------------------------------------------------------------------
Donovan J. Edye [www.edye.wattle.id.au]
Namadgi Systems [www.namsys.com.au]
Voice: +61 2 6285-3460
Fax: +61 2 6285-3459
TVisualBasic = Class(None);
Heard just before the 'Big Bang': "...Uh Oh...."
----------------------------------------------------------------------
GXExplorer [http://www.gxexplorer.org] Freeware Windows Explorer
replacement. Also includes freeware delphi windows explorer components.
----------------------------------------------------------------------

Re: [DUG]: sql... now i really need help

Reply via email to