Mark Forums Read

Complex unknown dept of select, maybe not so complex if you knowhow to do it

Go Back

comp.databases.postgresql comp.databases.postgresql

Reply

#1

Old

Johan Nel

Posts: n/a

Complex unknown dept of select, maybe not so complex if you knowhow to do it - 07-31-2008 , 11:08 PM

Hi all,

I have two pg tables that I try to define sub-forms and controls in:
Table:formdef
form_no serial,
form_id varchar(10)

Table:formdet
form_no references(formdef),
ctrl_id varchar(10)
ctrl_type character(1) check(ctrl_type in ('c', 'f'))

ctrl_type defines if ctrl_id is a sub-form or a control on form_no

What I would like to do is to get a list of all forms that are sub-forms
of a given (parameter passed) "main" form.

If I do:
select * from formdef where form_no = 1
union
select * from formdef where form_no in (select ctrl_id from formdet
where ctrl_type = 'f' and form_no = 1)

the result is giving me all the form and sub-forms for form_no = 1, but
what when I also want all sub-sub-forms and sub-sub[-sub[-sub]]...-forms
if they do exist without having to predetermine how many levels there
are defined?

Thanks in advance.

Johan Nel
Pretoria, South Africa.

Reply With Quote

#2

Old

Laurenz Albe

Posts: n/a

Re: Complex unknown dept of select, maybe not so complex if you know how to do it - 08-01-2008 , 02:07 AM

Johan Nel <johan555.nel555 (AT) remove_all_555 (DOT) xsinet555.co.za> wrote:

Quote:

I have two pg tables that I try to define sub-forms and controls in:
Table:formdef
form_no serial,
form_id varchar(10)

Table:formdet
form_no references(formdef),
ctrl_id varchar(10)
ctrl_type character(1) check(ctrl_type in ('c', 'f'))

ctrl_type defines if ctrl_id is a sub-form or a control on form_no

What I would like to do is to get a list of all forms that are sub-forms
of a given (parameter passed) "main" form.

If I do:
select * from formdef where form_no = 1
union
select * from formdef where form_no in (select ctrl_id from formdet
where ctrl_type = 'f' and form_no = 1)

the result is giving me all the form and sub-forms for form_no = 1, but
what when I also want all sub-sub-forms and sub-sub[-sub[-sub]]...-forms
if they do exist without having to predetermine how many levels there
are defined?

Either you wait for PostgreSQL 8.4, which will probably have recursive
queries, or you write a stored procedure that does it.

Yours,
Laurenz Albe

Reply With Quote

#3

Old

Laurenz Albe

Posts: n/a

Re: Complex unknown dept of select, maybe not so complex if you know how to do it - 08-01-2008 , 02:07 AM

Johan Nel <johan555.nel555 (AT) remove_all_555 (DOT) xsinet555.co.za> wrote:

Quote:

I have two pg tables that I try to define sub-forms and controls in:
Table:formdef
form_no serial,
form_id varchar(10)

Table:formdet
form_no references(formdef),
ctrl_id varchar(10)
ctrl_type character(1) check(ctrl_type in ('c', 'f'))

ctrl_type defines if ctrl_id is a sub-form or a control on form_no

What I would like to do is to get a list of all forms that are sub-forms
of a given (parameter passed) "main" form.

If I do:
select * from formdef where form_no = 1
union
select * from formdef where form_no in (select ctrl_id from formdet
where ctrl_type = 'f' and form_no = 1)

the result is giving me all the form and sub-forms for form_no = 1, but
what when I also want all sub-sub-forms and sub-sub[-sub[-sub]]...-forms
if they do exist without having to predetermine how many levels there
are defined?

Either you wait for PostgreSQL 8.4, which will probably have recursive
queries, or you write a stored procedure that does it.

Yours,
Laurenz Albe

Reply With Quote

#4

Old

Laurenz Albe

Posts: n/a

Re: Complex unknown dept of select, maybe not so complex if you know how to do it - 08-01-2008 , 02:07 AM

Johan Nel <johan555.nel555 (AT) remove_all_555 (DOT) xsinet555.co.za> wrote:

Quote:

I have two pg tables that I try to define sub-forms and controls in:
Table:formdef
form_no serial,
form_id varchar(10)

Table:formdet
form_no references(formdef),
ctrl_id varchar(10)
ctrl_type character(1) check(ctrl_type in ('c', 'f'))

ctrl_type defines if ctrl_id is a sub-form or a control on form_no

What I would like to do is to get a list of all forms that are sub-forms
of a given (parameter passed) "main" form.

If I do:
select * from formdef where form_no = 1
union
select * from formdef where form_no in (select ctrl_id from formdet
where ctrl_type = 'f' and form_no = 1)

the result is giving me all the form and sub-forms for form_no = 1, but
what when I also want all sub-sub-forms and sub-sub[-sub[-sub]]...-forms
if they do exist without having to predetermine how many levels there
are defined?

Either you wait for PostgreSQL 8.4, which will probably have recursive
queries, or you write a stored procedure that does it.

Yours,
Laurenz Albe

Reply With Quote

#5

Old

Laurenz Albe

Posts: n/a

Re: Complex unknown dept of select, maybe not so complex if you know how to do it - 08-01-2008 , 02:07 AM

Johan Nel <johan555.nel555 (AT) remove_all_555 (DOT) xsinet555.co.za> wrote:

Quote:

I have two pg tables that I try to define sub-forms and controls in:
Table:formdef
form_no serial,
form_id varchar(10)

Table:formdet
form_no references(formdef),
ctrl_id varchar(10)
ctrl_type character(1) check(ctrl_type in ('c', 'f'))

ctrl_type defines if ctrl_id is a sub-form or a control on form_no

What I would like to do is to get a list of all forms that are sub-forms
of a given (parameter passed) "main" form.

If I do:
select * from formdef where form_no = 1
union
select * from formdef where form_no in (select ctrl_id from formdet
where ctrl_type = 'f' and form_no = 1)

the result is giving me all the form and sub-forms for form_no = 1, but
what when I also want all sub-sub-forms and sub-sub[-sub[-sub]]...-forms
if they do exist without having to predetermine how many levels there
are defined?

Either you wait for PostgreSQL 8.4, which will probably have recursive
queries, or you write a stored procedure that does it.

Yours,
Laurenz Albe

Reply With Quote

#6

Old

Laurenz Albe

Posts: n/a

Re: Complex unknown dept of select, maybe not so complex if you know how to do it - 08-01-2008 , 02:07 AM

Johan Nel <johan555.nel555 (AT) remove_all_555 (DOT) xsinet555.co.za> wrote:

Quote:

I have two pg tables that I try to define sub-forms and controls in:
Table:formdef
form_no serial,
form_id varchar(10)

Table:formdet
form_no references(formdef),
ctrl_id varchar(10)
ctrl_type character(1) check(ctrl_type in ('c', 'f'))

ctrl_type defines if ctrl_id is a sub-form or a control on form_no

What I would like to do is to get a list of all forms that are sub-forms
of a given (parameter passed) "main" form.

If I do:
select * from formdef where form_no = 1
union
select * from formdef where form_no in (select ctrl_id from formdet
where ctrl_type = 'f' and form_no = 1)

the result is giving me all the form and sub-forms for form_no = 1, but
what when I also want all sub-sub-forms and sub-sub[-sub[-sub]]...-forms
if they do exist without having to predetermine how many levels there
are defined?

Either you wait for PostgreSQL 8.4, which will probably have recursive
queries, or you write a stored procedure that does it.

Yours,
Laurenz Albe

Reply With Quote

#7

Old

Laurenz Albe

Posts: n/a

Re: Complex unknown dept of select, maybe not so complex if you know how to do it - 08-01-2008 , 02:07 AM

Johan Nel <johan555.nel555 (AT) remove_all_555 (DOT) xsinet555.co.za> wrote:

Quote:

I have two pg tables that I try to define sub-forms and controls in:
Table:formdef
form_no serial,
form_id varchar(10)

Table:formdet
form_no references(formdef),
ctrl_id varchar(10)
ctrl_type character(1) check(ctrl_type in ('c', 'f'))

ctrl_type defines if ctrl_id is a sub-form or a control on form_no

What I would like to do is to get a list of all forms that are sub-forms
of a given (parameter passed) "main" form.

If I do:
select * from formdef where form_no = 1
union
select * from formdef where form_no in (select ctrl_id from formdet
where ctrl_type = 'f' and form_no = 1)

the result is giving me all the form and sub-forms for form_no = 1, but
what when I also want all sub-sub-forms and sub-sub[-sub[-sub]]...-forms
if they do exist without having to predetermine how many levels there
are defined?

Either you wait for PostgreSQL 8.4, which will probably have recursive
queries, or you write a stored procedure that does it.

Yours,
Laurenz Albe

Reply With Quote

#8

Old

Laurenz Albe

Posts: n/a

Re: Complex unknown dept of select, maybe not so complex if you know how to do it - 08-01-2008 , 02:07 AM

Johan Nel <johan555.nel555 (AT) remove_all_555 (DOT) xsinet555.co.za> wrote:

Quote:

I have two pg tables that I try to define sub-forms and controls in:
Table:formdef
form_no serial,
form_id varchar(10)

Table:formdet
form_no references(formdef),
ctrl_id varchar(10)
ctrl_type character(1) check(ctrl_type in ('c', 'f'))

ctrl_type defines if ctrl_id is a sub-form or a control on form_no

What I would like to do is to get a list of all forms that are sub-forms
of a given (parameter passed) "main" form.

If I do:
select * from formdef where form_no = 1
union
select * from formdef where form_no in (select ctrl_id from formdet
where ctrl_type = 'f' and form_no = 1)

the result is giving me all the form and sub-forms for form_no = 1, but
what when I also want all sub-sub-forms and sub-sub[-sub[-sub]]...-forms
if they do exist without having to predetermine how many levels there
are defined?

Either you wait for PostgreSQL 8.4, which will probably have recursive
queries, or you write a stored procedure that does it.

Yours,
Laurenz Albe

Reply With Quote

#9

Old

Laurenz Albe

Posts: n/a

Re: Complex unknown dept of select, maybe not so complex if you know how to do it - 08-01-2008 , 02:07 AM

Johan Nel <johan555.nel555 (AT) remove_all_555 (DOT) xsinet555.co.za> wrote:

Quote:

I have two pg tables that I try to define sub-forms and controls in:
Table:formdef
form_no serial,
form_id varchar(10)

Table:formdet
form_no references(formdef),
ctrl_id varchar(10)
ctrl_type character(1) check(ctrl_type in ('c', 'f'))

ctrl_type defines if ctrl_id is a sub-form or a control on form_no

What I would like to do is to get a list of all forms that are sub-forms
of a given (parameter passed) "main" form.

If I do:
select * from formdef where form_no = 1
union
select * from formdef where form_no in (select ctrl_id from formdet
where ctrl_type = 'f' and form_no = 1)

the result is giving me all the form and sub-forms for form_no = 1, but
what when I also want all sub-sub-forms and sub-sub[-sub[-sub]]...-forms
if they do exist without having to predetermine how many levels there
are defined?

Either you wait for PostgreSQL 8.4, which will probably have recursive
queries, or you write a stored procedure that does it.

Yours,
Laurenz Albe

Reply With Quote

Reply

« Previous Thread | Next Thread »

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.