Pulling Master-Detail data from Oracle w/o separate lines for detail

Question

Okay, I tried searching around, but couldn't come up with a decent idea on what to query on, so I figured I'd post a question. How can I pull back master/detail information without having the master data duplicated along with every line of detail data?

Given the following scenario:

Master - Cars
=============
decimal  car_id (pk)
string   name
decimal  cost
decimal  doors

Child - Colors
=============
decimal color_id (pk)
decimal car_id (fk)
string  color

So, given the above data, if I had a car that was available in Red, Green & Blue (I don't do champagne), the way I normally do things, I'd have 3 rows come back in my recordset.

Is there anyway to get all of the "colors" concatenated into a single field, or returned as an Array? That way I can just have the 1 row for each car?

Answer 1

This sounds like http://stackoverflow.com/questions/1651608/sql-joining-question (a question I asked a while back. Got a good answer)

Answer 2

It seems to me that your COLORS table is trying to be both a reference and intersection table, but if that's how you have it then:

select car_id, ltrim(sys_connect_by_path(color,','),',') colors 
  from 
   (select car_id, color, row_number() over (partition by car_id order by color) rn
          from colors 
       ) rn
  where connect_by_isleaf=1
  connect by prior rn+1=rn and prior car_id = car_id 
  start with rn=1
;

should work