• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

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 ??
0
Vrtnar
Asked:
Vrtnar
  • 4
1 Solution
 
ziolkoCommented:
select * from workers where CompanyID = value_selected_in1table AND SubcompanyID = value_selected_in2table

ziolko.
0
 
bokistCommented:
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


   
0
 
VrtnarAuthor Commented:
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'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ziolkoCommented:
SQL.Text:= 'select * from workers  where companyID = ' + ABSTable1.FieldByName('companyID').AsString + ' AND SubcompanyID = ' +ABSQuery1.FieldByName('SubcompanyID').AsString

ziolko.
0
 
ziolkoCommented:
...or

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

ziolko.
0
 
ziolkoCommented:
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now