SQL query problem.

[rpk2006]

I am having some problem in creating a SQL query.
I have two tables, CUSTOMER and ITEM. Following are the fields
of two tables:
-------------------------------------
CUSTOMER ITEM
-------- ----
BillNo ItemName
CustomerID ItemRate
Item_Name
Quantity
-------------------------------------

I want to extract all records where BILLNO = <user entered value>
and AMOUNT which is calculated from QUANTITY in CUSTOMER table
and ITEMRATE in ITEM table.

I have tried query like this:
---------------------------------------------
SELECT Customer.Item_Name, (Customer.Quantity * Item.ItemRate)
AS Amount FROM Customer, Item WHERE BillNo = num;
---------------------------------------------
Here &quot;num&quot; is the value which the user will enter at run-time.

This query is returning correct result, but it is returning
those retrieved records multiple times.

Please guide me.



---------------------------------
Securing a computer system has traditionally been a battle of wits: the penetrator tries to find the holes, and the designer tries to close them. � M.Gosser

 

[swampBoogie]

SELECT Customer.Item_Name, 
       Customer.Quantity * Item.ItemRate AS Amount 
  FROM Customer, Item 
 WHERE BillNo = num
   and customer.item_name = item.item_name

 

[carp]

swampBoogie has pointed the way for you.

The problem you were encountering was a cartesian join - created by the fact that you never specified the condition for joining the two tables. Consequently, for each row in one table, you will get a join on every row in the other table.