SQL JOINT checking..

[beginner81]

I want to check whether a certain record is exists in database or not using the parameter CustName and Product(which is the value that pass from other form).
I'd write the SQL stament as following :


function TDataModule.IsExists(CustName: String; Product:String): Boolean;
begin
with qry do
begin
Close;
SQL.Clear;
SQL.Add('SELECT * FROM CUSTOMER AS A1 INNER JOIN PRODUCT AS A2 ON A1.CUSTOMER_ID=A2.CUSTOMERID');
SQL.Add('WHERE CustName=:CUstName');
SQL.Add('AND Product=roduct');
ParamByName('CustName').AsString := CustName;
ParamByName('Product').AsString := Product;
Open;
result := not IsEmpty;
end;
end;


CUSTOMER and PRODUCT is different table, so i think i need to use the joint statement(correct me if i'm wrong)..
But the code is seems like not working correctly .. can anyone help me find out the problem ?? is the way i use the
Join statement correct ?? Hope can hear from ya guys soon.. and thx in advance for ya help..

 

[towerbase]

I think you need to supply more information.

What DBMS are you using (Paradox, MySQL, MS SQL Server etc) ?

What you say it is "not working correctly" what happens?

Is it a compile time problem?

Is it a run time problem?

What error messages do you get?

Andrew

 

[beginner81]

I'm using MsSQL database..
No error actually .. I'm checking whether the record is existing? If not exist then i'll add it as a record.. but some of the time even though the record is exist, still it will added to the database...
is the JOINT SQL statement that i provide above correct ?? thx again..

 

[towerbase]

I've never used MS SQL so I can't help you on the syntax of the supported statements.

However, it appears to me that your database design is not optimal. It would be unusual to have both customer name and product name in the same table. It would also be unusual to have customer name as a unique key to that table.

Has your database design been normalised?

Andrew

 

[VintageWine]

Product seems to be a table and field name. OK, but makes reading code confusing. Try adding aliases in the where clause. Also, in the join clause you have spelt customer_id differently - is that intentional?

Also use COUNT(*) to fetch count of records rather than all the data. If the query returned many rows, it would be unncessaryily slow.

Try

SELECT COUNT(*)
FROM CUSTOMER A1
INNER JOIN PRODUCT A2 ON A1.CUSTOMER_ID=A2.CUSTOMER_ID
WHERE A1.CustName=:CustName
AND A2.Product=roduct
....
....
Result:=Fields[0].asInteger>0;

 

[DaZZleD]

the SQL statement should be something like this:
SQL.Add('SELECT count(*) as occurancesNr FROM CUSTOMER a, PRODUCT b WHERE a.CUSTOMER_ID = b.CUSTOMERID AND a.CustName = "' + custName + '" AND b.PRODUCT = "' + product + '"');

once you use the qry.Open command, you get the number of occurances using qry.FieldByName('occurancesNr').AsInteger propery.

post back if you still need help.

 

[beginner81]

tHANK YOU GUYS SO MUCH .. IT'S WORKING NOW... APPRECIATE SO MUCH ..