asked on

afterscroll queery

I have 3 tables.'Companies','Subcompanies' and 'Workers'

1st one :
CompanyID
CompanyName

second :
CompanyID
SubcompanyID
SubcompanyName

Third :
CompanyID
SubcompanyID
WorkerID
Workername

On afterscroll of the 1st table
with ABSQuery1 do
begin
Close;
if ABSTable1.FieldByName('CompanyID').AsString <> '' then
begin
SQL.Text:= 'select * from Subcompanies where CompanyID = ' +
ABSTable1.FieldByName('CompanyID').AsString;
Open;
end;
end;

However now I need to have workers displayed.
I want displayed only those who's CompanyID and SubcompanyID match.
That means another queery on afterscroll of the first ABSQueery1.
How is the sql supposed to look like ??

Lukasz Zielinski

select * from workers where CompanyID = value_selected_in1table AND SubcompanyID = value_selected_in2table

ziolko.

bokist

Hi vrtnar !

Try this way:

if ABSTable1.FieldByName('CompanyID').AsString <> '' then
begin
Sql.Clear;
Sql.add('select c.companyId, companyName, s.subcompanyId, subcompanyName, workerId, Workername');
Sql.add(' from companies c, subcompanies s, workers w');
Sql.add('where c.companyId = :co');
Sql.add(' and c.companyId = s.companyId');
Sql.add(' and s.companyId = w.companyId');
Sql.add(' and s.subcompanyId = w.subcompanyId');
Parambyname('co').asstring := ABSTable1.FieldByName('CompanyID').AsString;
Open;

Not tested, just from head!!

regards
steve

Vrtnar

ASKER

if ABSQuery1.FieldByName('SubcompanyID').AsString <> '' then
begin
SQL.Text:= 'select * from workers where companyID = ABSTable1.FieldByName('companyID').AsString AND ABSQuery1.FieldByName('SubcompanyID').AsString ;

I get :
[Error] Unit1.pas(191): Missing operator or semicolon
[Error] Unit1.pas(192): Expression expected but 'AND' found
[Error] Unit1.pas(192): Unterminated string
[Error] Unit1.pas(193): Missing operator or semicolon
[Error] Unit1.pas(317): Statement expected but 'PROCEDURE' found
[Fatal Error] Project1.dpr(8): Could not compile used unit 'Unit1.pas'

ASKER CERTIFIED SOLUTION

Lukasz Zielinski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lukasz Zielinski

...or

SQL.Text := Format('select * from workers where CompanyId=%d and SubCompanyID=%d', [ABSTable1.FieldByName('companyID').AsInteger, ABSQuery1.FieldByName('SubcompanyID').AsInteger]);

ziolko.

Lukasz Zielinski

btw. cursor position in ABSTable and ABSQuery doesn't correspond with index of record selected in DBGrid or any other component that you use to display data, go to Demos folder and look for Master/Detail sample

ziolko.