Re: [SQL] subselect prob in view
On Monday 21 Jun 2004 4:11 pm, Gary Stainburn wrote: > On Monday 21 Jun 2004 3:19 pm, Tom Lane wrote: > > Gary Stainburn <[EMAIL PROTECTED]> writes: > > > from requests r, users u, request_types t, > > > request_states s, dealerships d, departments de, customers c > > > left outer join (select co_r_id, count(co_r_id) from comments > > > group by co_r_id) co on > > > co.co_r_id = r.r_id > > > psql:goole1.sql:45: ERROR: Relation "r" does not exist > > > > I think you have learned some bad habits from MySQL :-( > > > > PostgreSQL follows the SQL spec and makes JOIN bind more tightly than > > comma. Therefore, in the above the LEFT JOIN is only joining "c" to > > "co" and its JOIN ON clause can only reference those two relations. > > > > You could get the behavior you seem to expect by changing each comma > > in the from-list to CROSS JOIN. Then the JOINs all bind left-to-right > > and so "r" will be part of the left argument of the LEFT JOIN. > > > > Note that if you are using a pre-7.4 release this could have negative > > effects on performance --- see the user's guide concerning how explicit > > JOIN syntax constrains the planner. > > > > regards, tom lane > > Thanks for this Tom, but I've never used MySQL. > > I'll look at the docs and have another go. > > Gary In order to simplify things, I'm just concentrating on the view to give me the two tallies. The two selects work seperately, but I'm still getting the syntax for the combined quiery wrong. I'm asuming that the problem's before the 'on' clause and not the clause itself (I've also tried using 'using' instead but that didn't work either. goole=# select co_id, co_r_id, cor_viewed goole-# from comments c, co_recipients co goole-# where c.co_id = co.cor_co_id; co_id | co_r_id | cor_viewed ---+-+--- 1 | 1 | 2004-06-22 10:15:52.945065+01 1 | 1 | 2004-06-22 10:15:52.952895+01 2 | 1 | 2 | 1 | 3 | 2 | (5 rows) goole=# select co_r_id, count(co_r_id) from comments group by co_r_id; co_r_id | count -+--- 1 | 2 2 | 1 (2 rows) goole=# select co_r_id, count(co_r_id) from comments where co_id in goole-# (select distinct co_id goole(#from comments c, co_recipients co goole(#where c.co_id = co.cor_co_id and co.cor_viewed is null) goole-# group by co_r_id; co_r_id | count -+--- 1 | 1 2 | 1 (2 rows) goole=# select co.co_r_id, co.count as com_count, cor.count as com_unseen goole-# from goole-# (select co_r_id, count(co_r_id) goole(# from comments group by co_r_id) co, goole-# (select co_r_id, count(co_r_id) goole(# from comments where co_id in goole(# (select distinct co_id goole(# from comments c, co_recipients co goole(# where c.co_id = co.cor_co_id and co.cor_viewed is null) goole(# group by co_r_id) cor on co.co_r_id = cor.co_r_id; ERROR: parser: parse error at or near "on" goole=# -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] subselect prob in view
On Tue, 22 Jun 2004, Gary Stainburn wrote: > On Monday 21 Jun 2004 4:11 pm, Gary Stainburn wrote: > > On Monday 21 Jun 2004 3:19 pm, Tom Lane wrote: > > > Gary Stainburn <[EMAIL PROTECTED]> writes: > > > > from requests r, users u, request_types t, > > > > request_states s, dealerships d, departments de, customers c > > > > left outer join (select co_r_id, count(co_r_id) from comments > > > > group by co_r_id) co on > > > > co.co_r_id = r.r_id > > > > psql:goole1.sql:45: ERROR: Relation "r" does not exist > > > > > > I think you have learned some bad habits from MySQL :-( > > > > > > PostgreSQL follows the SQL spec and makes JOIN bind more tightly than > > > comma. Therefore, in the above the LEFT JOIN is only joining "c" to > > > "co" and its JOIN ON clause can only reference those two relations. > > > > > > You could get the behavior you seem to expect by changing each comma > > > in the from-list to CROSS JOIN. Then the JOINs all bind left-to-right > > > and so "r" will be part of the left argument of the LEFT JOIN. > > > > > > Note that if you are using a pre-7.4 release this could have negative > > > effects on performance --- see the user's guide concerning how explicit > > > JOIN syntax constrains the planner. > > > > > > regards, tom lane > > > > Thanks for this Tom, but I've never used MySQL. > > > > I'll look at the docs and have another go. > > > In order to simplify things, I'm just concentrating on the view to give me the > two tallies. The two selects work seperately, but I'm still getting the > syntax for the combined quiery wrong. I'm asuming that the problem's before > the 'on' clause and not the clause itself (I've also tried using 'using' > instead but that didn't work either. > > goole=# select co.co_r_id, co.count as com_count, cor.count as com_unseen > goole-# from > goole-# (select co_r_id, count(co_r_id) > goole(# from comments group by co_r_id) co, > goole-# (select co_r_id, count(co_r_id) > goole(# from comments where co_id in > goole(# (select distinct co_id > goole(# from comments c, co_recipients co > goole(# where c.co_id = co.cor_co_id and co.cor_viewed is null) > goole(# group by co_r_id) cor on co.co_r_id = cor.co_r_id; AFAICS, you're not using the join syntax between co and cor at the outer level. There's nothing to attach that on clause to. Either you want that in a where clause (like in the subselect above it) or you want to use "inner join" between co and cor. ---(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
Re: [SQL] subselect prob in view
Gary Stainburn <[EMAIL PROTECTED]> writes: > The two selects work seperately, but I'm still getting the > syntax for the combined quiery wrong. What you've got here reduces to select co.co_r_id, co.count as com_count, cor.count as com_unseen from (select ...) co, (select ...) cor on co.co_r_id = cor.co_r_id; which is invalid because "ON something" must be associated with JOIN. You could write either of select co.co_r_id, co.count as com_count, cor.count as com_unseen from (select ...) co join (select ...) cor on co.co_r_id = cor.co_r_id; select co.co_r_id, co.count as com_count, cor.count as com_unseen from (select ...) co, (select ...) cor where co.co_r_id = cor.co_r_id; but you can't mix-and-match. With an inner join there isn't any semantic difference between ON and WHERE, so it's a matter of taste which to use. But with outer joins there's a big difference. regards, tom lane ---(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
Re: [SQL] plpgsql - Insert from a record variable?
Phil> execute ''column_values := Phil> column_values || quote_literal(r.'' || cr.column_name || '')''; basic> FOR rec IN EXECUTE basic> ''select column_values || quote_literal(r.'' || basic> cr.column_name || '') alias column_values''; LOOP basic> column_values := rec.column_values; basic> END LOOP; I think your code will try to execute a query like this: select column_values || quote_literal(r.something) alias column_values This will fail because column_values and r are both plpgsql variables, and so are not visible to the SQL interpreter. Any other suggestions? --Phil. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] matching rows differing only by fkey,pkey
I'm duplicating some fields in the table but the duplicates will have a new primary key and a new foreign key. For example, Table "b" looks like this: bid (pkey default value is a sequence) aid (fkey) field1 field2 field3 INSERT INTO b (aid, field1, field2, field3) SELECT 23, field1, field2, field3 from b where aid = 22; "b" is the middle table of a many to many relationship. The end result is to duplicate the data for a particular record in table "a" so that all of it's related data in tables "b" and "c" is duplicated. When the relationships are one to one or one to many this process is easy, however sometimes there's a many to many relationship. It seems that a helpful tool would be a query that can return just the pkey of the original record copied from and the pkey of the newly created record. For example, if the b table looked like this after a copy of 3 rows: bid | aid | field1 | field2 | field3 1 | 22 | abc| 123| abc123 2 | 22 | xyz| 456| xyz456 3 | 22 | pdq| 789| pdq789 4 | 23 | abc| 123| abc123 5 | 23 | xyz| 456| xyz456 6 | 23 | pdq| 789| pdq789 I'd like to get this: oldbid | newbid 1 | 4 2 | 5 3 | 6 Any ideas? If someone has an alternate method of achieving the same result I'd be excited to hear about it. Matthew Nuzum | ISPs: Make $200 - $5,000 per referral by www.followers.net | recomending Elite CMS to your customers! [EMAIL PROTECTED] | http://www.followers.net/isp ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] matching rows differing only by fkey,pkey
Matthew Nuzum wrote: When the relationships are one to one or one to many this process is easy, however sometimes there's a many to many relationship. It seems that a helpful tool would be a query that can return just the pkey of the original record copied from and the pkey of the newly created record. For example, if the b table looked like this after a copy of 3 rows: bid | aid | field1 | field2 | field3 1 | 22 | abc| 123| abc123 2 | 22 | xyz| 456| xyz456 3 | 22 | pdq| 789| pdq789 4 | 23 | abc| 123| abc123 5 | 23 | xyz| 456| xyz456 6 | 23 | pdq| 789| pdq789 I'd like to get this: oldbid | newbid 1 | 4 2 | 5 3 | 6 SELECT one.bid AS oldbid, two.bid AS newbid FROM b one, b two WHERE one.field1=two.field1 AND ... AND two.bid > one.bid ; Of course, if there are 3+ rows with duplicate field1/2/3 then this won't work. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] matching rows differing only by fkey,pkey
On Tue, Jun 22, 2004 at 12:34:35 -0400, Matthew Nuzum <[EMAIL PROTECTED]> wrote: > Any ideas? If someone has an alternate method of achieving the same result > I'd be excited to hear about it. A more precise formulation of what exactly you are doing might be helpful. >From your description it sounds like you should already know the primary key of the row you are copying and can use currval to get the id of the row just inserted. I get the impression that you are manually using these values instead of having a script do it. But it should be easy to write a script that does this for you. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] matching rows differing only by fkey,pkey
Thanks for your suggestion to use curval(), that will be useful. I'd entirely forgotten about that function. That likely will help when combined with Richard's suggestion and an idea I already had. I'm sorry that the problem wasn't clearer. The best way I can describe it is like this: The end result is to duplicate the data for a particular record in table "a" so that all of it's related data in tables "b" and "c" is duplicated. Where "b" is the middle table in a many to many relationship. For example, - a - b - c aid <--+ bid <--+ cid data1 +-->aid +-->bid data2 field1 info1 date3 field2 info2 SELECT a.*,b.*,c.* from a,b,c where b.aid = a.aid and c.bid = b.bid; So, the goal is to duplicate an object that is made up of the data stored across these three tables. Any suggestions? Matthew Nuzum | ISPs: Make $200 - $5,000 per referral by www.followers.net | recomending Elite CMS to your customers! [EMAIL PROTECTED] | http://www.followers.net/isp ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] matching rows differing only by fkey,pkey
Bruno Wolff III wrote: On Tue, Jun 22, 2004 at 13:40:03 -0400, Matthew Nuzum <[EMAIL PROTECTED]> wrote: The end result is to duplicate the data for a particular record in table "a" so that all of it's related data in tables "b" and "c" is duplicated. Where "b" is the middle table in a many to many relationship. For example, - a - b - c aid <--+ bid <--+ cid data1 +-->aid +-->bid data2 field1 info1 date3 field2 info2 SELECT a.*,b.*,c.* from a,b,c where b.aid = a.aid and c.bid = b.bid; So, the goal is to duplicate an object that is made up of the data stored across these three tables. [snip] Offhand I can't think of a way to avoid using a procedural language to walk through the b table so that you can easily keep track of which new bid corresponds to which old bid. However, writing a perl script or plsql function to do this for you shouldn't be difficult. Screams out plpgsql to me - it's good at automating what is basically cut & paste of values. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
