Dont you want SQL that is:
SELECT employment.name AS JOB, skill.name AS 'SKILL REQUIRED'
FROM empolyment, skill, skillneeded
WHERE skillneeded.skillid = skill.skillid AND
employment.companyid = skill.companyid AND
employment.groupid = skill.groupid
GROUP BY employment.name, skill.name
That would return a dataset as follows:
JOB(employment.name) SKILL REQUIRED(skill.name)
-------------------------------------------------------------------------------------------------------------------
SUPERVISORS HAZ SUB
SUPERVISORS FARTING
SUPERVISORS LONG TIME
SUPERVISORS GHOSTING
SUPERVISORS FORKLIFT LICENCE
SUPERVISORS FIRST AID CERT
FORKLIFT DRIVER HAZ SUB
FORKLIFT DRIVER FIRST AID CERT
OFFICE BITCH FARTING
OFFICE BITCH GHOSTING
If you were using say a DevExpress DataGrid (http://www.devexpress.com/products/vcl/f_g402.asp) then you could use the grid to group on JOB and visually you would have what is required. I suspect you are trying to get a result set back here that you can just dump into DB aware controls and it will look "good" SQL is not really good at that. You are always going to get set orientated data (as per my result set) that is not always going to go well into your DB aware controls. You may have to introduce a layer on top of your data retrieval that you can pass to your presentation layer.
At 13:28 14/02/2003 +1300, you wrote:
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.
----------------------------------------------------------------------
----------------------------------------------------------------------
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.
----------------------------------------------------------------------
