Board index » delphi » Re: INNER JOIN Using ADOQuery

Re: INNER JOIN Using ADOQuery


2006-03-30 11:12:01 PM
delphi200
"eddela" <XXXX@XXXXX.COM>writes
Quote
In a small App, I am using an ADOQuery component to inner join two
MSAccess tables and do a query involving fields from both tables. The SQL
property of the ADOQuery has this statement.

SELECT Item, Description, Vendor FROM Table1, Table2

WHERE Table1.Item = Table2.Item

This statement is right out of the text book, yet the following error
message appears. "The specified field 'Item' could refer to more than one
table listed in the FROM clause of your SQL statement."

Of course it does, but why is the exception raised? Please help. Thanks
You and I know that it doesn't matter which table the Item field comes from,
but the parser doesn't and is incapable of making such a decision. Always
be specific about which table a field comes from, even when there's
currently no question. Table layouts have a habit of changing, even in
seemingly trivial applications.
SELECT t1.Item, t2.Description, t2.Vendor FROM Table1 t1, Table2 t2 WHERE
t1.Item = t2.Item;
"Eschew Ambiguity". :)
--
Ray Marron
 
 

Re: INNER JOIN Using ADOQuery

 In a small App, I am using an ADOQuery component to inner join two
MSAccess tables and do a query involving fields from both tables. The SQL
property of the ADOQuery has this statement.
SELECT Item, Description, Vendor FROM Table1, Table2
WHERE Table1.Item = Table2.Item
This statement is right out of the text book, yet the following error
message appears. "The specified field 'Item' could refer to more than one
table listed in the FROM clause of your SQL statement."
Of course it does, but why is the exception raised? Please help. Thanks
--- posted by geoForum on delphi.newswhat.com
 

Re: INNER JOIN Using ADOQuery

Quote
"eddela" <XXXX@XXXXX.COM>writes
news:442bec8b$XXXX@XXXXX.COM...
>In a small App, I am using an ADOQuery component to inner join two
>MSAccess tables and do a query involving fields from both tables. The
SQL
>property of the ADOQuery has this statement.
>
>SELECT Item, Description, Vendor FROM Table1, Table2
>
>WHERE Table1.Item = Table2.Item
>
>This statement is right out of the text book, yet the following error
>message appears. "The specified field 'Item' could refer to more than
one
>table listed in the FROM clause of your SQL statement."
>
>Of course it does, but why is the exception raised? Please help.
Thanks

You and I know that it doesn't matter which table the Item field comes
from,
but the parser doesn't and is incapable of making such a decision.
Always
be specific about which table a field comes from, even when there's
currently no question. Table layouts have a habit of changing, even in
seemingly trivial applications.

SELECT t1.Item, t2.Description, t2.Vendor FROM Table1 t1, Table2 t2
WHERE
t1.Item = t2.Item;

"Eschew Ambiguity". :)

--
Ray Marron

Thanks Ray, Your format works like a charm!

--- posted by geoForum on delphi.newswhat.com
 

Re: INNER JOIN Using ADOQuery

"eddela" <XXXX@XXXXX.COM>writes
[SNIP & dreadful geoForum quoting manually cleaned up]
Quote
Another question about this. Suppose I want to add another where
statement,such as
WHERE t1.Item = t2.Item AND t1.Item = x
ORDER BY t2.Vendor
{where x is a certain product name entered by the user into an
editbox.}
If I use the following format, the code executes correctly, without
the WHERE and ORDER BY statemente, When the WHERE and/or ORDER BY
statements are added, I get the same error messages as before. Please
tell me what's missing or wrong? Thank you again. Ed
I can not tell you exactly what's wrong unless you post the exact code that's
generating the error message.
My intuition tells me you're probably not putting quotes around x, which
would lead the parser to think it is a field name and not be able to
determine the table it came from.
I suggest you read about query field parameters (TParam[s]). They take care
of things like quoting and date formatting automatically. Then you could do
something like this:
Query.SQL.Text := 'SELECT t1.item, t2.vendor FROM table1 t1, table2 t2 WHERE
t1.item = t2.item AND t1.foo = :Foo';
Query.ParamByName('Foo').AsString := FooEdit.Text;
Query.Open;
--
Ray Marron
 

Re: INNER JOIN Using ADOQuery

Quote
>"eddela" <XXXX@XXXXX.COM>writes
>news:442bec8b$XXXX@XXXXX.COM...
>>In a small App, I am using an ADOQuery component to inner join two
>>MSAccess tables and do a query involving fields from both tables. The
SQL
>>property of the ADOQuery has this statement.
>>
>>SELECT Item, Description, Vendor FROM Table1, Table2
>>
>>WHERE Table1.Item = Table2.Item
>>
>>This statement is right out of the text book, yet the following error
>>message appears. "The specified field 'Item' could refer to more than
one
>>table listed in the FROM clause of your SQL statement."
>>
>>Of course it does, but why is the exception raised? Please help.
Thanks
>
>You and I know that it doesn't matter which table the Item field comes
from,
>but the parser doesn't and is incapable of making such a decision.
Always
>be specific about which table a field comes from, even when there's
>currently no question. Table layouts have a habit of changing, even in
>seemingly trivial applications.
>
>SELECT t1.Item, t2.Description, t2.Vendor FROM Table1 t1, Table2 t2
WHERE
>t1.Item = t2.Item;
>
>"Eschew Ambiguity". :)
>
>--
>Ray Marron
>
>Thanks Ray, Your format works great in response to a button click!
>Another question about this. Suppose I want to add another where
statement,such as
WHERE t1.Item = t2.Item AND t1.Item = x
ORDER BY t2.Vendor
{where x is a certain product name entered by the user into an
editbox.}
Quote
If I use the following format, the code executes correctly, without
the WHERE and ORDER BY statemente, When the WHERE and/or ORDER BY
statements are added, I get the same error messages as before. Please
tell me what's missing or wrong? Thank you again. Ed
Quote


--- posted by geoForum on delphi.newswhat.com

--- posted by geoForum on delphi.newswhat.com
 

Re: INNER JOIN Using ADOQuery

Quote
"eddela" <XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...
[SNIP & dreadful geoForum quoting manually cleaned up]
>Another question about this. Suppose I want to add another where
>statement,such as
>WHERE t1.Item = t2.Item AND t1.Item = x
>ORDER BY t2.Vendor
>{where x is a certain product name entered by the user into an
>editbox.}
>If I use the following format, the code executes correctly, without
>the WHERE and ORDER BY statemente, When the WHERE and/or ORDER BY
>statements are added, I get the same error messages as before. Please
>tell me what's missing or wrong? Thank you again. Ed

I can not tell you exactly what's wrong unless you post the exact code
that's
generating the error message.
My intuition tells me you're probably not putting quotes around x, which
would lead the parser to think it is a field name and not be able to
determine the table it came from.

I suggest you read about query field parameters (TParam[s]). They take
care
of things like quoting and date formatting automatically. Then you
could do
something like this:

Query.SQL.Text := 'SELECT t1.item, t2.vendor FROM table1 t1, table2 t2
WHERE
t1.item = t2.item AND t1.foo = :Foo';
Query.ParamByName('Foo').AsString := FooEdit.Text;
Query.Open;

--
Ray Marron

Ray, Here is the code I am using, in response to a btn click. I made two
changes and now the complete code works. First, I put T1. before the
field name in the variable b assignment. Secondly, I removed the T1. from
the variable x, because it doesn't represent a field name. All is right
with the world again. I appreciate your help. Thanks, Ed.
var a, x ,b, c : string;
begin
a := ' = ' ;
b := ' T1.Item ' ;
c := ' ASC ' ;
begin
if EntFld.text = ' ' then
ShowMessage(' Please enter the selected Group Letter. ')
else
x := #39 + EntFld.Text + #39;
end;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select T1.ProdGrp, T1.Vendor,
T1.Item, T2.Description');
ADOQuery1.SQL.Add('From T1,T2' );
ADOQuery1.SQL.Append('WHERE' );
ADOQuery1.SQL.Append('T1.Item=T2.Item' );
ADOQuery1.SQL.Append('and T1.ProdGrp ' );
ADOQuery1.SQL.Append(a);
ADOQuery1.SQL.Append(x );
ADOQuery1.SQL.Append('Order By' );
ADOQuery1.SQL.Append(b);
ADOQuery1.SQL.Append(c);
ADOQuery1. Open;
end;
--- posted by geoForum on delphi.newswhat.com