From: Andre Schubert <andre@km3.de>
To: Ludwig Lim <lud_nowhere_man@yahoo.com>
Cc: andre@km3.de
Cc: pgsql-sql@postgresql.org
Subject: Re: Need Help for select
Date: Wed, 14 Aug 2002 09:10:53 +0200
Message-ID: <20020814091053.1d3138db.andre@km3.de> (raw)
In-Reply-To: <20020812151148.26186.qmail@web40013.mail.yahoo.com>
References: <20020812150640.4acd538b.andre@km3.de>
<20020812151148.26186.qmail@web40013.mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andre@km3.de, lud_nowhere_man@yahoo.com
Subject: Re: Need Help for select
In-Reply-To: <20020814091053.1d3138db.andre@km3.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox