Re: Need Help for select - Mailing list pgsql-sql

On Mon, 12 Aug 2002 08:11:48 -0700 (PDT)
"Ludwig Lim" <lud_nowhere_man@yahoo.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@km3.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