[SQL] 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_idb_id | d_idid | name |--- ---|- ---|- -| 1 | A_Name11 | 1 1 | 1 1 | D_Name1 2 | A_Name21 | 2 2 | 1 2 | D_Name2 3 | A_Name32 | 1 3 | 2 3 | D_Name3 4 | A_Name43 | 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Need Help for select
On Mon, 12 Aug 2002 08:11:48 -0700 (PDT) "Ludwig Lim" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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_idb_id | d_idid | > > name > > |--- ---|- ---|- > > -| > > 1 | A_Name11 | 1 1 | 1 1 | > > D_Name1 > > 2 | A_Name21 | 2 2 | 1 2 | > > D_Name2 > > 3 | A_Name32 | 1 3 | 2 3 | > > D_Name3 > > 4 | A_Name43 | 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Need Help for select
On Wed, 14 Aug 2002 21:57:02 +0900 "Masaru Sugawara" <[EMAIL PROTECTED]> wrote: > On Wed, 14 Aug 2002 09:10:53 +0200 > Andre Schubert <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Need Help for select
On Thu, 15 Aug 2002 11:17:15 +0900 "Masaru Sugawara" <[EMAIL PROTECTED]> wrote: > On Wed, 14 Aug 2002 16:04:21 +0200 > Andre Schubert <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help on indexing timestamps
On Fri, 7 Mar 2003 15:03:01 -0200 (GMT+2)
Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
> On Fri, 7 Mar 2003, Andre Schubert wrote:
>
> > On Fri, 7 Mar 2003 14:17:36 -0200 (GMT+2)
> > Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
> >
> > > On Fri, 7 Mar 2003, Andre Schubert wrote:
> > >
> > > > On Fri, 7 Mar 2003 13:48:04 -0200 (GMT+2)
> > > > Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
> > > >
> > > > > On Fri, 7 Mar 2003, Andre Schubert wrote:
> > > > >
> > > > > > On Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2)
> > > > > > Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
> > > > > >
> > > > > > > On Fri, 7 Mar 2003, Andre Schubert wrote:
> > > > > > >
> > > > > > > > Hi all,
> > > > > > > >
> > > > > > > > i have a little problem on indexing a table which contains
> > > > > > > > about 4 millions of traffic-data.
> > > > > > > > My problem is, that a want to select all data from
> > > > > > > > a specific month from a specific ip and this select should use the
> > > > > > > > index.
> > > > > > > > I use the following select:
> > > > > > >
> > > > > > > Did you try to use BETWEEN ??
> > > > > > > E.g.
> > > > > > > ... and time_stamp between '2003-01-01 00:00:00'::timestamp and
> > > > > > > '2003-02-01 00:00:00'::timestamp
> > > > > > >
> > > > > >
> > > > > > Yes and it works if i write the dates by hand, every new month.
> > > > > > But the query is executed automatically and i dont want
> > > > > > to write in the dates before the query is executed. Maybe the
> > > > > > the start and enddate should also be alculated with sql,
> > > > > > because i want to create a view from this statement and execute it every
> > > > > > month.
> > > > > > Or did i miss something.
> > > > >
> > > > > You could have an index on the
> > > > > whole
> > > > > date_trunc('month',tbl_traffic.time_stamp),ip
> > > > >
> > > > > How does it perform?
> > > > >
> > > >
> > > > I'am not sure how to create such an index...
> > > >
> > > > First: create or replace function trunc_ip(timestamp with time zone) returns
> > > > timestamptz as
> > > >'select date_trunc(''month'',$1)' language 'sql' with (iscachable);
> > > > Then: create index idx_test on tbl_traffic using btree( trunc(time_stamp) );
> > > >
> > > > Result: db_km3aue=# explain analyze select sum(inet_up+inet_down) from
> > > > tbl_traffic where trunc(tbl_traffic.time_stamp) =
> > > > trunc('2003-02-01'::timestamptz) and ip = '80.243.40.56';
> > > > NOTICE: QUERY PLAN:
> > > >
> > > > Aggregate (cost=108.78..108.78 rows=1 width=16) (actual time=2278.48..2278.48
> > > > rows=1 loops=1)
> > > > -> Index Scan using idx_test on tbl_traffic (cost=0.00..108.78 rows=1
> > > > width=16) (actual time=0.23..2240.50 rows=5346 loops=1)
> > > > Total runtime: 2278.62 msec
> > > >
> > > > Maybe the problem is, that the index is created without ip as the second
> > > > column
> > >
> > > Sorry, it sliped my mind that we cannot have compound indexes on
> > > functions. :(
> > >
> > > Anyway.
> > > Did the explicit BETWEEN gave you satisfactory performance?
> > >
> >
> > Yes if i use BETWEEN and type in the dates by hand the query takes about 200ms
> > with explain analyze.
> > I think these is a good performance.
> > But if i use now() instead of manually typed dates the query take about 1400ms :(
> > I thought the somebody posted to this list, that now() is a function that is not
> > cached,
> > and thatswhy does not work pretty well with indexes.
> > I created a cached function cached_now() which returns now() but is declared with
> > "isCacheable".
> > If i use cached_now() instead of now the query takes also about 200ms :)
> > Is it safe, or better could it have any side-effects, if i use my "own"
> > cached_now() in such a query ?
>
> I think you should use a high level language to wrap your PostgreSQL
> access (e.g. java, c, perl, ).
>
> Since you are gonna be running this query as a batch job,
> you must use a high level lang that can handle dates in
> a UNIX-like way. (milisecs from 1970-01-01 00:00:00)
>
Ok, i think you are right.
Thanks for the quick advice.
Regards, as
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Help on indexing timestamps
On Mon, 10 Mar 2003 10:12:15 +0100
Tomasz Myrta <[EMAIL PROTECTED]> wrote:
> Andre Schubert wrote:
> > Hi all,
> >
> > i have a little problem on indexing a table which contains
> > about 4 millions of traffic-data.
> > My problem is, that a want to select all data from
> > a specific month from a specific ip and this select should use the index.
> > I use the following select:
> >
> > db_nmkm3aue=# explain analyze select sum(inet_up+inet_down) from tbl_traffic where
> > date_trunc('month',tbl_traffic.time_stamp)::timestamptz = date_trunc('month',now()
> > - timespan('1 months')) and ip = '80.243.38.57';
> > NOTICE: QUERY PLAN:
> >
> > Aggregate (cost=116.30..116.30 rows=1 width=16) (actual time=1620.79..1620.79
> > rows=1 loops=1)
> > -> Index Scan using idx_ip_time_stamp on tbl_traffic (cost=0.00..116.30 rows=1
> > width=16) (actual time=1216.79..1579.89 rows=5232 loops=1)
> > Total runtime: 1620.94 msec
> >
> > But it takes a long time to select the traffic for all Ips.
> > Is there a way to select these data with using the index correctly ?
> >
> > Thanks in advance
> >
> I have one more solution - try to rewrite your where clause to NOT USE
> function on time_stamp. If your query will look like:
> select ... where time_stamp between (function with now() returning first
> day) and (function with now() returning last day);
> your index will work fine.
>
Thanks for the hint, i will test this and report to this list.
Thanks, as
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
