Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
SAFROLE YUTANI
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic