aspose file tools*
The moose likes JDBC and the fly likes How to construct an SQL query to return data from a 1-to-MANY relationship? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to construct an SQL query to return data from a 1-to-MANY relationship?" Watch "How to construct an SQL query to return data from a 1-to-MANY relationship?" New topic
Author

How to construct an SQL query to return data from a 1-to-MANY relationship?

SAFROLE YUTANI
Ranch Hand

Joined: Jul 06, 2001
Posts: 257
I cant believe I'm asking this question considering I thought I knew SQL, only I have not used it recently, hehehe, ok, here's the deal:
Lets say we have a CAR and a PART table, and the relationship between them is 1-to-many, in that a CAR contains many PARTs. The table CAR has the following simple attributes:
CAR.number
CAR.name
CAR.description
CAR.weight
And the PART table has the following attributes:
PART.number
PART.CAR.number (FK)
PART.name
PART.description
I'm trying to use 1 SQL statement to get all data for a given CAR, including all the PARTs. The problem I'm observing is that although the query works, there is no way to differentiate between the single row of data that contains the simple attributes of CAR and the remaining rows of data that contain the PARTs info. Of course, I can use two separate queries, one to get the CAR data, and another to get all PARTs associated with that car, but I dont want to hit the database twice for obvious reasons.
Any suggestions?
SAF
Napa Sreedhar
Ranch Hand

Joined: Jan 29, 2002
Posts: 62
String stmt = "SELECT C.number, C.name, C.description, C.weight, P.number, P.name, P.descrption
FROM CAR C, PART P
WHERE C.number = p.car_number"; // (foreign key to CAR table)
I think by using the aliases to tables you can identify what you want.
Please catch up if I could not understand your problem.
Napa
 
Consider Paul's rocket mass heater.
 
subject: How to construct an SQL query to return data from a 1-to-MANY relationship?