Selecting data from multiple tables

[Atul Mishra]

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]

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.

[Atul Mishra]

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]

Can any one suggest some ideas here ? Please..

[Hemanth Pallavajula]

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.