Re: help on query

From: Jean-Philippe Squelbut <squelbut_at_csi.com>
Date: 1998/09/02
Message-ID: <eONIi7k19GA.230_at_ntdwwaaw.compuserve.com>#1/1

You 'll have to fix the max of children you can get. For exemple : 5

select name, a.text_val
  from contacts, segment a
where id=a.id
union
  select name, a.text_val, b.text_val
  from contacts, segment a, segment b
where id=a.id
  and id=b.id
  and id=c.id
union
select name, a.text_val, b.text_val, c.text_val   from contacts, segment a, segment b, segment c where id=a.id
  and id=b.id
  and id=c.id
union
select name, a.text_val, b.text_val, c.text_val, d.text_val   from contacts, segment a, segment b, segment c, segment d where id=a.id

  and  id=b.id
  and  id=c.id
  and  id=d.id

union
select name, a.text_val, b.text_val, c.text_val, d.text_val, e.text_val   from contacts, segment a, segment b, segment c, segment d, segment e where id=a.id

  and  id=b.id
  and  id=c.id
  and  id=d.id
  and  id=e.id;

Marc Hansen a écrit dans le message <35EC2DCA.C7A1C060_at_iname.com>...
>I have situation where there is a parent table
>contact and child table segments. I am looking for
>a query or function that will return the parent id
>and the multiple child table rows on one line.
>
>example :
>table column1, column2,
>contacts id, name
>data 1, 'John Jones'
> 2, 'Mike Smith'
>
>table column1, column2
>segment id, text_val
>data 1, 'johns info 1'
> 1, 'johns info 2'
>
> 1, 'johns info 3'
>
> 2, 'mikes info 1'
>
>...
>
>where the results would something like
>1,John Jones, johns info 1,johns info 2,johns info
>3...
>2,Mike Smith,mikes info 1...
>
>
>I have not found and existing oracle function for
>this and I'm having time problems writing my own.
>replies to the group or direct email would be
>great.
>
>thanks
>Marc
>
>
>
Received on Wed Sep 02 1998 - 00:00:00 CEST