Hi I have two tables products and sales.Here product is a parent table and sales is a child table.product and sales have one-to-many relationship.I want to fetch this data using jdbc.I want to fetch all the products and their sales details.
what would be the efficient way to do this. say i want to display
Is it possible to fetch the data for a product using single query?Can i avoid duplication of data from product table
ex: if i use query
can i avoid duplication of product name in the resultset?
Which fields you expect to have? Since you have a row for each record in sales regard to a given product you get the above result. If you want to have only the sum of the quantity then you will get one record for each product which sums all the quantities from sales table for a given product (But you can't have sales date in it then since it's the sum of all).
select p.productname,sum(s.qty) from product p,sales s where p.productid=s.sales.id group by p.productname;
PS: Syntax may vary on your RDBMS [ December 17, 2008: Message edited by: Vijitha Kumara ]
Hi Vijitha I got what you are saying.Actually my requirement is that on UI I should display the the product name(only once) and under it all the sales data relatively.For that i am getting data by joining two tables.But it also leads to the duplication of product name in the resultset.So just wanted to know if it is possible to avoid this duplication??
Actually my requirement is that on UI I should display the the product name(only once) and under it all the sales data relatively
In that case you may have a simple logic to have the product, and iterate through the array (or whatever you are using or any other logic you are having ) and add those records under the product which are belongs to that particular product in the UI.
Joined: Feb 22, 2007
but i am getting the product name itself at runtime .so resultset contain duplicate value of prdouct name in all the rows related to that product.