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 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.
----------------------------------------------------------------------
