• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Junilu Lacar
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Piet Souris
  • Carey Brown
  • Stephan van Hulst
Bartenders:
  • Frits Walraven
  • fred rosenberger
  • salvin francis

Selecting data from multiple tables

 
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author
Posts: 4278
34
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can any one suggest some ideas here ? Please..
 
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic