Thread: Need Help for select
Hi all, i need help to build a select query or plpgsql-fucntion for the following tables. create table a ( id int, name varchar(20) ) create table b ( a_id int, c_id int ) create table c ( b_id int, d_id int ) create table d ( id int, name varchar(20) ) 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_Name12 | A_Name2 1 | 2 2 | 1 2 | D_Name23 | A_Name3 2 | 1 3 | 2 3 | D_Name34 | 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 Thanks in advance and sorry for my bad english
--- 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
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
On Wed, 14 Aug 2002 09:10:53 +0200 Andre Schubert <andre@km3.de> wrote: > 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. > If this mention implies that the tuple of (1, 1) in the c is supposed to take precedence over that of (2, 1), SELECT a.name, d.name FROM (SELECT DISTINCT min(b.a_id) AS a_id, c.d_id FROM b, c WHERE b.c_id = c.b_id GROUP BY b.c_id, c.d_id ) AS t, a, d WHERE a.id = t.a_id AND d.id = t.d_id Regards, Masaru Sugawara
On Wed, 14 Aug 2002 21:57:02 +0900 "Masaru Sugawara" <rk73@sea.plala.or.jp> wrote: > On Wed, 14 Aug 2002 09:10:53 +0200 > Andre Schubert <andre@km3.de> wrote: > > > 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. > > > > > If this mention implies that the tuple of (1, 1) in the c is supposed > to take precedence over that of (2, 1), I want to compare if a tuple in c exist in b for each c.d_id and b.a_id. In c exists 3 tuples: (1,2), (3,4), (5) and want to find these tuples in b. select a.name,d.name from a,b,c,d where "tuple found in b" = "tuple found in c" and b.a_id = a.id and c.d_id = d.id My english is not so good, but i hope you understand what i want to do. Thanks in advance > > > SELECT a.name, d.name > FROM (SELECT DISTINCT min(b.a_id) AS a_id, c.d_id > FROM b, c > WHERE b.c_id = c.b_id > GROUP BY b.c_id, c.d_id > ) AS t, > a, > d > WHERE a.id = t.a_id > AND d.id = t.d_id > > > > Regards, > Masaru Sugawara > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
On Wed, 14 Aug 2002 16:04:21 +0200
Andre Schubert <andre@km3.de> wrote:
> >
> >
> > If this mention implies that the tuple of (1, 1) in the c is supposed
^^ b
> > to take precedence over that of (2, 1),
>
> I want to compare if a tuple in c exist in b for each c.d_id and b.a_id.
> In c exists 3 tuples: (1,2), (3,4), (5)
> and want to find these tuples in b.
I were able to catch what conditions join b to c. I try to think again.
>
> select a.name,d.name from a,b,c,d where "tuple found in b" = "tuple found in c" and b.a_id = a.id and c.d_id = d.id
>
> My english is not so good, but i hope you understand what i want to do.
>
> Thanks in advance
>
> >
> >
> > SELECT a.name, d.name
> > FROM (SELECT DISTINCT min(b.a_id) AS a_id, c.d_id
> > FROM b, c
> > WHERE b.c_id = c.b_id
> > GROUP BY b.c_id, c.d_id
> > ) AS t,
> > a,
> > d
> > WHERE a.id = t.a_id
> > AND d.id = t.d_id
> >
> >
Regards,
Masaru Sugawara
On Wed, 14 Aug 2002 16:04:21 +0200 Andre Schubert <andre@km3.de> wrote: > I want to compare if a tuple in c exist in b for each c.d_id and b.a_id. > In c exists 3 tuples: (1,2), (3,4), (5) > and want to find these tuples in b. Probably I would think I have reached the correct query. Table b and c, however, must have unique indices like the following in order to get the result by using it, because it pays no attention to the duplicate keys. If there are no primary keys, it will become more complicated for eliminating duplicate keys. create table b ( a_id int, c_id int, constraint p_key_b primary key(a_id, c_id) ); create table c ( b_id int, d_id int, constraint p_key_c primary key(b_id, d_id) ); SELECT a.name, d.name FROM (SELECT t2.a_id, t2.d_id FROM (SELECT b.a_id, t1.d_id, t1.n FROM (SELECT c.b_id, c.d_id, t0.n FROM c, (SELECT d_id, COUNT(*) AS n FROM c GROUP BY d_id)AS t0 WHERE c.d_id = t0.d_id ) AS t1 LEFT OUTER JOINb ON (t1.b_id = b.c_id) WHERE b.a_id IS NOT NULL ) AS t2 GROUP BY t2.a_id, t2.d_id, t2.n HAVING COUNT(*) = t2.n ) AS t3, a, d WHERE a.id = t3.a_id AND d.id = t3.d_id Regards, Masaru Sugawara
On Thu, 15 Aug 2002 11:17:15 +0900 "Masaru Sugawara" <rk73@sea.plala.or.jp> wrote: > On Wed, 14 Aug 2002 16:04:21 +0200 > Andre Schubert <andre@km3.de> wrote: > > > > I want to compare if a tuple in c exist in b for each c.d_id and b.a_id. > > In c exists 3 tuples: (1,2), (3,4), (5) > > and want to find these tuples in b. > > > Probably I would think I have reached the correct query. Table b and c, > however, must have unique indices like the following in order to get the > result by using it, because it pays no attention to the duplicate keys. > If there are no primary keys, it will become more complicated for eliminating > duplicate keys. > > > create table b ( > a_id int, > c_id int, > constraint p_key_b primary key(a_id, c_id) > ); > create table c ( > b_id int, > d_id int, > constraint p_key_c primary key(b_id, d_id) > ); > > > SELECT a.name, d.name > FROM (SELECT t2.a_id, t2.d_id > FROM (SELECT b.a_id, t1.d_id, t1.n > FROM (SELECT c.b_id, c.d_id, t0.n > FROM c, (SELECT d_id, COUNT(*) AS n > FROM c GROUP BY d_id) AS t0 > WHERE c.d_id = t0.d_id > ) AS t1 > LEFT OUTER JOIN b ON (t1.b_id = b.c_id) > WHERE b.a_id IS NOT NULL > ) AS t2 > GROUP BY t2.a_id, t2.d_id, t2.n > HAVING COUNT(*) = t2.n > ) AS t3, > a, > d > WHERE a.id = t3.a_id > AND d.id = t3.d_id > After days of studying this query and hours of testing i would say this query works for me very well. Thank you very very much. > > > Regards, > Masaru Sugawara > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly