File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Fetching data from parent and  child tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Fetching data from parent and  child tables" Watch "Fetching data from parent and  child tables" New topic
Author

Fetching data from parent and child tables

raj malhotra
Ranch Hand

Joined: Feb 22, 2007
Posts: 285
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
productName(from product table), quantiy (from sales table),salesdate(from sales table)

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?

thanks
Raj
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3829

Originally posted by raj malhotra:

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?

thanks
Raj


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 ]

SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
raj malhotra
Ranch Hand

Joined: Feb 22, 2007
Posts: 285
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??
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3829

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.
raj malhotra
Ranch Hand

Joined: Feb 22, 2007
Posts: 285
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.

thanks
Raj
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
group your result set by your product name by using the GROUP BY clause.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Fetching data from parent and child tables