wood burning stoves 2.0*
The moose likes JDBC and the fly likes Selecting data from multiple tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Selecting data from multiple tables " Watch "Selecting data from multiple tables " New topic
Author

Selecting data from multiple tables

Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
All,

Here is the scenario: My JSP page displays records from 3 different tables, Table A,B and C.

Table A has three fields as PK and for Tables B and Tables C, these three fields are PK and FK.

The result from Table A is always 1 record. But there can be 1 to many records from other two tables.

I tried using JOINS, but the number of results which I get is 1 to many.Since its one query, the resultSet iterates through the results and gives many records always depending on any many records.

So, I tried using three sql statements,3 resultsets,3 dataobjects and then a vector to pass the result. Am I doing the right thing here ?

Thanks
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

I'm a little confused about what you are trying to do. Could you post some SQL queries and possibly the table structure to clarify this? If you don't want records of the 1-many variety you could do a group by clause which gives you single records based on combinations of multiple records.


My Blog: Down Home Country Coding with Scott Selikoff
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Scott,

Here is a sample of how the exact tables look

Table A:
ID
ID1
ID2
Name

Table B:
ID ID1 ID2 Phone

Table C
ID ID1 ID2 Car

I want to get ID,ID1,ID2,Name,Phone,Car as output.

ID,ID1,ID2 are PK in Table A and also FK to Table B and Table C.
Also there will be only one Name as output and
there can be one to many phones as well as one to many cars as outputs.

Right now, I am doing three queries

SELECT NAME FROM TABLE A WHERE
ID LIKE '' AND ID1 LIKE 'XXX' AND ID2 LIKE 'XXXX';

SELECT PHONE FROM TABLE B WHERE
ID LIKE '' AND ID1 LIKE 'XXX' AND ID2 LIKE 'XXXX';

SELECT CAR FROM TABLE C WHERE
ID LIKE '' AND ID1 LIKE 'XXX' AND ID2 LIKE 'XXXX';

My question is, is there a better way to accomplish the results rather than using three separate SQL statetements.

For this I tried JOINS,
But multiple values are returned for Name.

Hope I explained it well.

Thanks.
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Can any one suggest some ideas here ? Please..
Hemanth Pallavajula
Ranch Hand

Joined: Oct 07, 2004
Posts: 38
Hi Atul,

I too has faced certain situations like this.

My suggestion is:

1. Take data using Joins statement.
2. Instatiate a String as nameStr, and two ArrayList objects as phonesList & carsList.

3. Now as you get data from the query, for the first row of the resultset:
3.a) Set value of "Name" column to nameStr.
3.b) Set value of "phone" column to phonesList
3.c) Set value of "car" column to carsList.

4. For subsequent rows from the resultset:
4.a) If value for "phone" column is already present in phonesList, ignore. Else, add it to the list.
4.b) If value for "car" column is already present in carsList, ignore. Else, add it to the list.

5. After the entire resultset is processed, you will get name, phonesList, carsList.


Cheers,<br />Hemanth...<br />(When opportunity doesn't knock, build a door.)
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Selecting data from multiple tables