Re: Need Help for select

On Mon, 12 Aug 2002 08:11:48 -0700 (PDT)
"Ludwig Lim" <lud_nowhere_man(at)yahoo(dot)com> wrote:

Hi,

thank you for your quick response, but the answer you gave me
doesnt give the result i want.
Let me try to explain what i want.
Lets say that table a contains informations about
some items of the type foo.
Table b holds information about what item bar is selected by foo.
In short: each item foo can have 0..n items bar selected.
Thats the left side.

The ride side as follows.
Table d contains information about subitems.
Table c holds information about subitems and items of type bar.
Each subitem can have 0..n items bar selected.

What i want is that a subitem is only activated for a foo item if
the foo-item has exactly selected the same bar items selected as
as the relation between table c and d.

Example 1:
The foo-item A_Name1 has selected the bar-items 1 and 2.
The subitem D_Name1 is only activated for a foo-item if that foo-item
has selected the bar-items 1 and 2, this happens for A_Name1.

Example 2:
The foo-item A_Name4 has selected the bar-item 5.
The subitem D_Name3 is only activated for a foo-item if that foo-item
has selected the bar-item 5, this happens for A_Name4.

Hope these informations describe my problema little bit better.
I have played with some plpgsql-functions but found no way.
The problem is the 0..n relation between a+b and c+d.

Regards

andre

>
> --- Andre Schubert <andre(at)km3(dot)de> wrote:
> > Hi all,
> >
> > i need help to build a select query or
> > plpgsql-fucntion
> > for the following tables.
> >> Is it possible to build a select query that selects
> > d.name for each a.name where
> > a.id = b.a_id and d.id = c.d_id and each b.c_id must
> > exist in c.b_id.
> >
> > Example:
> > a: b: c : d:
> > id | name a_id | c_id b_id | d_id id |
> > name
> > ----|------- -------|----- -------|-----
> > -----|--------
> > 1 | A_Name1 1 | 1 1 | 1 1 |
> > D_Name1
> > 2 | A_Name2 1 | 2 2 | 1 2 |
> > D_Name2
> > 3 | A_Name3 2 | 1 3 | 2 3 |
> > D_Name3
> > 4 | A_Name4 3 | 3 4 | 2
> > 3 | 4 5 | 3
> > 4 | 5
> >
> > i wish to have to following result:
> > --------|--------
> > A_Name1 | D_Name1
> > A_Name3 | D_Name2
> > A_Name4 | D_Name3
> >
> > I hope someone could understand the problem
>
> You can use views to to simplify complicated queries
>
> Create a view that will join table A & B
>
> Create view view_ab(name,id) as
> select name,c_id
> from a,b
> where id = c_id;
>
> Create a view that will join table C & D
>
> Create view view_cd(name2,id2) as
> select name,b_id
> from c,d
> where id=d_id;
>
> Create a query that will join the views "view_ab" and
> "view_cd"
>
> Select name,name2
> from view_ab,view_cd
> where id=id2;
>
>
>
> __________________________________________________
> Do You Yahoo!?
> HotJobs - Search Thousands of New Jobs
> http://www.hotjobs.com