Start Free Trial

asked on

ado SQL load info from 2 differnt tables into same memo

table1:= customerinfo field=EstimateID

table2:= Estimates field=EstimateID

I need to load info from both tables above into the same memo, But only if the EstimateID from both tables are the same.
Now i am getting a sql syntax error but I do not think I am going about it write.

procedure TForm1.ToolButton6Click(Sender: TObject);
begin
  datamodule1.AQEstimates.SQL.Clear;
  datamodule1.AQEstimates.SQL.Text:='Select EstimateID From Estimates where EstimateID= 1 and Select EstimateID From customerinfo where EstimateID= 1';
  datamodule1.AQEstimates.Open;
 
    while not datamodule1.AQEstimates.Eof do
     begin
     memo1.Text:=(datamodule1.AQEstimates.FieldByName('EstimateID').AsVariant);
     memo1.Text:=(datamodule1.AQEstimates.FieldByName('Description').AsString);
     memo1.Text:=(datamodule1.AQcustomerinfo.fieldbyname('EstimateID').AsString);
     memo1.Text:=(datamodule1.AQcustomerinfo.fieldbyname('Full Name').AsString);
     datamodule1.AQEstimates.Next;
     datamodule1.AQcustomerinfo.Next;
     end;
 
end;

Open in new window

1) You should also post this question in the SQL Zone
2) You are probably looking for an SQL JOIN statement., I haven't tested this but I'll give it a try

SELECT customerinfo."Full Name", Estimates."Description" FROM customerinfo JOIN Estimates ON customerinfo.EstimateID = customerinfo.EstimateID;

Try this way:

with datamodule1.AQEstimates do
begin
Sql.Clear;
Sql.Add('select e.estimateId, e.description, c.full name');
Sql.Add(' from estimates e, customerinfo c');
Sql.Add(' where e.estimateId = c.edtimateId')';
Sql.Add(' and e.estimateId = 1');
Open;
while not EOF do
begin
memo1.Text:= FieldByName('EstimateID').AsVariant;
memo1.Text:= FieldByName('Description').AsString;
memo1.Text:= fieldbyname('EstimateID').AsString;
memo1.Text:= fieldbyname('Full Name').AsString;
Next;
end;
end;

bokist> some problems with your solution...
namely calling first on the dataset,
and appending lines to the memo instead of replacing all the memo text each time...

Here are the changes
with datamodule1.AQEstimates do
begin
Sql.Clear;
Sql.Add('select e.estimateId, e.description, c.full name');
Sql.Add(' from estimates e, customerinfo c');
Sql.Add(' where e.estimateId = c.edtimateId')';
Sql.Add(' and e.estimateId = 1');
Open;
First;
while not EOF do
begin
memo1.Lines.Append(FieldByName('EstimateID').AsVariant);
memo1.Lines.Append(FieldByName('Description').AsString);
memo1.Lines.Append(fieldbyname('EstimateID').AsString);
memo1.Lines.Append(fieldbyname('Full Name').AsString);
Next;
end;
end

ASKER

bokist that compiles but Unspecified error.

ASKER

can you experts help me understand the sql
select e.estimateId, e.description, c.full name');
Sql.Add(' from estimates e, customerinfo c');
Sql.Add(' where e.estimateId = c.edtimateId')';
Sql.Add(' and e.estimateId = 1');
I never seen e.description or c.fullname how can you use e. or c. in a sql statment ?

ASKER

rfwolf same error.......

I'm not sure about bokist's SQL statement. Test it in design time in the SQL property of your query and open up the query from the object inspector. Also then check if the result set you are getting is the one you expect.

"can you experts help me understand the sql"
Well like I say, I'm not positive about bokist's SQL - he never uses the word join and yet it's a join statement using the WHERE clause...
But I can explain the use of e and c. These are your tables. When you mention your tables in the FROM clause in the SQL, you can proceed the table name with an abbreviation, here's an example:

SELECT A.Surname FROM Customers A;

ASKER

I put the sql text in the query of the object and set to active to true and same error unspecified error . It must be in the sql text.

ASKER

this is what i have in there.
select
e.estimateId, e.description, c.full name
from estimates e, customerinfo c
where e.estimateId = c.edtimateId and e.estimateId = 1

ASKER

SELECT A.Surname FROM Customers A;
so surname is a field ?
so after that I can now use A in any of the sql and it will equal=Surname.

NO... A will be the table "Customers"

Did you try my SQL?
SELECT customerinfo."Full Name", Estimates."Description" FROM customerinfo JOIN Estimates ON customerinfo.EstimateID = customerinfo.EstimateID;

ASKER

wolf i put that sql in the object inspector spl text and
Syntax error in from clause........

Whoops I see one error in my code in the ON clause.. here is the correction
SELECT customerinfo."Full Name", Estimates."Description" FROM customerinfo JOIN Estimates ON customerinfo.EstimateID = Estimates.EstimateID;

and indeed it does seem possible to do a join using the WHERE clause and not using the word 'join'

rwoolf !

I'm not beginer in SQL, and indeed I use INNER and OUTER JOIN depend on task.
Thank you for suggestion, perhaps better think about field name "full name" and not about my sql statement.

Go ahead an take offense bokist, I think I was very diplomatic in expressing that I thought there was a possible error. But I see I've hurt your delicate ego. And there's a reason why I put "full name" in quotes.

ASKER

crap it still gives syntax error in from clause.

What database are you using?

ASKER

bokist i like your example code to. but still an error.
Unspecified error. ANy ideals for me to fix this.
Thanks

rfwoolf!

when you said :
I'm not positive about bokist's SQL - he never uses the word join...
then you mean
I think I was very diplomatic in expressing...
Perhaps I misunderstand your words.

with datamodule1.AQEstimates do
begin
Sql.Clear;
Sql.Add('select e.estimateId, e.description, c."full name"');
Sql.Add(' from estimates e, customerinfo c');
Sql.Add(' where e.estimateId = c.edtimateId')';
Sql.Add(' and e.estimateId = 1');
Open;
// you can ignore First
while not EOF do
begin
memo1.lines.add(fieldByName('EstimateID').asstring); // or asinteger
memo1.lines.add(fieldByName('Description').asstring);
memo1.lines.add(fieldbyname('EstimateID').asstring);
memo1.lines.add(fieldbyname('Full Name').asstring);
Next;
end;
end;

ASKER

see attached.
error.jpg

What. database. are. you. using?
and this is a good reason why you shouldn't have spaces in your field names. Some databases will accept quotes around field names with spaces (e.g. Firebird), but yours may not. You may have to try using square brackets [full name] or single-quotes 'full name' or if your database is very stupid you may be forced to change your field name.

ASKER

it is an access 2003 database.

ASKER

does any expers know how to join the 2 tables so i can querry this ?

ASKER

Can anyone help to solve this problem . I think i need to join the tables .

ASKER

begin
with datamodule1.AQEstimates do
begin
Sql.Clear;
Sql.Add('select e.estimateId, e.description, c.[fullname]');
Sql.Add(' from estimates e, customerinfo c');
Sql.Add(' where e.estimateId = c.edtimateId');
Sql.Add(' and e.estimateId = 1');
Open;
// you can ignore First
while not EOF do
begin
memo1.lines.add(fieldByName('EstimateID').asstring); // or asinteger
memo1.lines.add(fieldByName('Description').asstring);
memo1.lines.add(fieldbyname('EstimateID').asstring);
memo1.lines.add(fieldbyname('Full Name').asstring);
Next;
end;
end;
This throughs error Parameter c.estimateID has no defalt value........

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

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

Start Free Trial

ASKER

Thanks for working with me.