|
never mind, sorted all the rest out myself..
the resulting query =
SELECT employment.name AS JOB, skill.name AS
SKILL_REQUIRED
FROM empLOyment, skill, skillneeded
join personemployment
on personemployment.employmentid = employment.employmentid
where
skillneeded.skillid = skill.skillid
AND
(employment.companyid = 1
or
employment.groupid in(Select Level1GroupID from company where companyid =
1))
and personemployment.personid =
1
and skillneeded.employmentid = employment.employmentid
and employmentid
in
(select employmentid from personemployment where personid=1)
GROUP BY
employment.name, skill.name
*PHEW*
thanks heaps for your help DONAVAN
XX
----- Original Message -----
Sent: Monday, February 17, 2003 9:51 AM
Subject: Re: [DUG]: sql... now i really need help
OOOH, and another important point to note,
the records returned are not accurate.
for example, office bitch only requires farting and
ghosting, but this query returns
| OFFICE BITCH |
|
|
|
|
FARTING |
|
|
OFFICE BITCH |
|
|
|
|
GHOSTING |
|
|
OFFICE BITCH |
|
|
|
|
HAZ SUB |
|
|
OFFICE BITCH |
|
|
|
|
LONG TIME |
and supervisors have 5 skills required against them
but this query returns
| SUPERVISORS |
|
|
|
|
FIRST AID CERT |
|
|
SUPERVISORS |
|
|
|
|
FORKLIFT LICENCE |
----- Original Message -----
Sent: Monday, February 17, 2003 9:00 AM
Subject: Re: [DUG]: sql... now i really need help
Getting close now, thanks. I only
want to return those jobs (and subsequent related skills) that a person is held
against in the person employment table.
ie person 1 may only be an office bitch, so i will
only want that employment returned. so the query now looks
like:
SELECT employment.name AS JOB, skill.name AS
SKILL_REQUIRED
FROM empLOyment, skill, skillneeded
join personemployment
on personemployment.employmentid = employment.employmentid
WHERE
skillneeded.skillid = skill.skillid
AND
(employment.companyid = skill.companyid
OR
employment.groupid =
skill.groupid)
and personid = 1
GROUP BY employment.name, skill.name
I need to get employment.employmentid from this
query too, however when i trY to add another row to the query (AS BELOW) i
get a
Dynamic SQL
Error SQL error code = -104 invalid column reference
SELECT employment.name AS JOB, skill.name AS SKILL_REQUIRED,
employment.employmentid AS EMPLOYMENTID
FROM empLOyment, skill,
skillneeded
join personemployment on personemployment.employmentid =
employment.employmentid
WHERE skillneeded.skillid = skill.skillid
AND
(employment.companyid = skill.companyid
OR
employment.groupid =
skill.groupid)
and personid = 1
GROUP BY employment.name,
skill.name
----- Original Message -----
Sent: Friday, February 14, 2003 4:05 PM
Subject: Re: [DUG]: sql... now i really need help
T,
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
----------------------------------------------------------------------
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.
----------------------------------------------------------------------
|
