• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
reply
    Bookmark Topic Watch Topic
  • New Topic