Re: [SQL] join against a function-result fails

On Jul 27, 2012, at 21:57, Andreas wrote > Hi, > I have a table with user ids and names. > Another table describes some rights of those users and still another one > describes who inherits rights from who. > > A function all_rights ( user_id ) calculates all rights of a user recursively > and

Re: [SQL] join returns too many results...

On Sun, Oct 03, 2010 at 02:54:41PM -0400, Frank Bax wrote: > Whatever clause I choose to add I *must* ensure that final result set > contains only one-to-one join between tables. Either of these two > results is acceptable: > > For option 1; result C=All or C=Centre is acceptable. > For option

Re: [SQL] join table problem

Howdy! At any rate, say I have 3 tables: table plant id:integer name string table seed_supplier id: integer company_name: string table plant_seed_supplier plant_id seed_supplier_id plant_seed_supplier is a join table that supports a many to many relationship between the plant table an

Re: [SQL] join with an array

Στις Wednesday 24 February 2010 15:34:48 ο/η Louis-David Mitterrand έγραψε: > Here is a test case I built. I want to list all cruises by cruise_type > but after merging cruise_type that have the same cruise_type_name: > > drop table cruise; > drop table cruise_type; > > create table cruise_type

Re: [SQL] join with an array

On Wed, Feb 24, 2010 at 02:09:09PM +0100, A. Kretschmer wrote: > In response to Louis-David Mitterrand : > > Hi, > > > > I'm trying the following query: > > > > select array_agg(t1.id) from table1 t1 join table2 t2 on (t2.id = > > any(array_agg)) group by t1.col1; > > > > but I get this err

Re: [SQL] join with an array

In response to Louis-David Mitterrand : > Hi, > > I'm trying the following query: > > select array_agg(t1.id) from table1 t1 join table2 t2 on (t2.id = > any(array_agg)) group by t1.col1; > > but I get this error: ERROR: column "array_agg" does not exist > > I tried aliasing array_agg(t

Re: [SQL] Join Advice and Assistance

Stephen, > If you want to return a single row for each user, regardless of the number > of email addresses, you might use ARRAY() with a subquery, eg (haven't > tested this to make sure it completely works): Your query worked perfectly! > Of course, this will return the addresses as a character

Re: [SQL] Join Advice and Assistance

To expand on Rob's reply: If you want to return a single row for each user, regardless of the number of email addresses, you might use ARRAY() with a subquery, eg (haven't tested this to make sure it completely works): SELECT u.*, um.*, ARRAY(SELECT emailaddr FROM user_emailaddrs em WHERE em.user

Re: [SQL] Join Advice and Assistance

My mistake. Should answer these things late at night. I think you will find that arrays will be your friend[s] On 02/22/2010 08:51 AM, Gary Chambers wrote: Rob, Thanks for the reply... If you want records for user without email addresses you will need an outer join on user_emailaddrs /* un

Re: [SQL] Join Advice and Assistance

Rob, Thanks for the reply... > If you want records for user without email addresses you will need an outer > join on user_emailaddrs > > /* untested */ > select u.userId, u.lname, u.lastname ,m.startdate, a.emailaddr > from users u > join usermetas m on u.userid = m.userid > left join user_emaila

Re: [SQL] Join Advice and Assistance

Gary Chambers wrote: All, I've encountered a mental block due primarily to my inexperience with moderately complex joins. Given the following three tables: Table "public.users" Column | Type | Modifiers ---++--

Re: [SQL] join help

f_rrd_id = 444) FiveM ON (OneM.f_timestamp = FiveM.f_timestamp) where OneM.f_rrd_id = 444 ORDER BY OneM.f_timestamp; From: Justin To: Kashmir Cc: pgsql-sql@postgresql.org Sent: Wednesday, April 8, 2009 9:38:22 PM Subject: Re: [SQL] join help Kashmir

Re: [SQL] join help

Kashmir wrote:only difference is: first table stores data per 'f_rrd_id' evey 5min, and the second table every single minute. I want to run a query that would return for the same 'f_rrd_id' all values from both tables sorted by f_timestamp, of course a set would only have values from the 5m

Re: [SQL] JOIN and aggregate problem

On Mon, 23 Feb 2009 15:44:05 + Richard Huxton wrote: > Tarlika Elisabeth Schmitz wrote: > > On Fri, 20 Feb 2009 19:06:48 + > > Richard Huxton wrote: > >> try something like: > >> > >> SELECT t1.d, t1.s, t1.c, count(*) > >> FROM t1 > >> LEFT JOIN ( > >>SELECT d,s,c FROM t2 WHERE x > >

Re: [SQL] JOIN and aggregate problem

Tarlika Elisabeth Schmitz wrote: > On Fri, 20 Feb 2009 19:06:48 + > Richard Huxton wrote: >> try something like: >> >> SELECT t1.d, t1.s, t1.c, count(*) >> FROM t1 >> LEFT JOIN ( >> SELECT d,s,c FROM t2 WHERE x >> ) AS t2_true USING (d,s,c) >> GROUP BY t1.d, t1.s, t1.c; >> >> Warning - no

Re: [SQL] JOIN and aggregate problem

On Fri, 20 Feb 2009 13:23:47 -0600 Bob Henkel wrote: > CREATE UNIQUE INDEX idx01_t1 > ON t1 USING btree (d, s, c); > > [...] > > SELECT t1.d, t1.s, t1.c, CASE WHEN t2.x IS NULL THEN 0 ELSE COUNT(*) > END FROM t1 > LEFT OUTER JOIN t2 > ON t1.d = t2.d >AND t1.s = t2.s >AND

Re: [SQL] JOIN and aggregate problem

On Fri, 20 Feb 2009 19:06:48 + Richard Huxton wrote: > Tarlika Elisabeth Schmitz wrote: > > I have 2 tables T1 and T2 > > > > T1 has the columns: D, S, C. The combination of D,S,C is unique. > > T2 has the columns: D, S, C, and boolean X. The combination of > > D,S,C is not unique. > > > >

Re: [SQL] JOIN and aggregate problem

On Fri, 20 Feb 2009 11:15:09 -0800 (PST) Stephan Szabo wrote: > On Fri, 20 Feb 2009, Tarlika Elisabeth Schmitz wrote: > > > I have 2 tables T1 and T2 > > > > T1 has the columns: D, S, C. The combination of D,S,C is unique. > > T2 has the columns: D, S, C, and boolean X. The combination of > > D,

Re: [SQL] JOIN and aggregate problem

How about this? CREATE TABLE t1(d INT,s INT, c INT); CREATE UNIQUE INDEX idx01_t1 ON t1 USING btree (d, s, c); INSERT INTO t1 (d, s, c) VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5); CREATE TABLE t2(d INT,s INT, c INT, x boolean); INSERT INTO t2(d, s, c, x) VALUES (1,1,1,TRUE),(1,1,1,FALSE),

Re: [SQL] JOIN and aggregate problem

Scratch this one won't work for you. On Fri, Feb 20, 2009 at 1:03 PM, Bob Henkel wrote: > I might be missing something but does this solve your issue? > > CREATE TABLE t1(d INT,s INT, c INT); > > CREATE UNIQUE INDEX idx01_t1 > ON t1 USING btree (d, s, c); > > INSERT INTO t1 (d, s, c) > VALUES (1,

Re: [SQL] JOIN and aggregate problem

On Fri, 20 Feb 2009, Tarlika Elisabeth Schmitz wrote: > I have 2 tables T1 and T2 > > T1 has the columns: D, S, C. The combination of D,S,C is unique. > T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is > not unique. > > I need to produce the following result for every occurr

Re: [SQL] JOIN and aggregate problem

Tarlika Elisabeth Schmitz wrote: > I have 2 tables T1 and T2 > > T1 has the columns: D, S, C. The combination of D,S,C is unique. > T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is > not unique. > > I need to produce the following result for every occurrence of T1: > D,S,C

Re: [SQL] JOIN and aggregate problem

I might be missing something but does this solve your issue? CREATE TABLE t1(d INT,s INT, c INT); CREATE UNIQUE INDEX idx01_t1 ON t1 USING btree (d, s, c); INSERT INTO t1 (d, s, c) VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4); CREATE TABLE t2(d INT,s INT, c INT, x boolean); INSERT INTO t2(d, s, c, x

Re: [SQL] JOIN results of refcursor functions

Milan Oparnica wrote: Then I've tried: CREATE FUNCTION foo(insklid int, out sklid int, out elid INT) RETURNS SETOF record AS $$ BEGIN RETURN QUERY SELECT sklid,elid FROM skladkol; RETURN; END; $$ LANGUAGE plpgsql; but it returns 5498 rows (which is exact number of rows in that table) bu

Re: [SQL] JOIN results of refcursor functions

> > Then I've tried: > > CREATE FUNCTION foo(insklid int, out sklid int, out elid INT) RETURNS SETOF > record AS $$ > BEGIN >RETURN QUERY SELECT sklid,elid FROM skladkol; >RETURN; > END; > $$ LANGUAGE plpgsql; know bug :( - your variable names are in collision with column names. You have t

Re: [SQL] JOIN results of refcursor functions

Alvaro Herrera wrote: Milan Oparnica escribió: I've searched documentation (8.3) and didn't find a way to use OUT variables in same manner as SETOF (RETURN NEXT doesn't create a record type result). Can you please give an example of how to return select fld1, fld2 from table through OUT

Re: [SQL] JOIN results of refcursor functions

Milan Oparnica escribió: > I've searched documentation (8.3) and didn't find a way to use OUT > variables in same manner as SETOF (RETURN NEXT doesn't create a record > type result). > > Can you please give an example of how to return select fld1, fld2 from > table through OUT variables so t

Re: [SQL] JOIN results of refcursor functions

Alvaro Herrera wrote: Milan Oparnica escribió: Tom Lane wrote: Milan Oparnica <[EMAIL PROTECTED]> writes: Is there any way to use INNER, LEFT and RIGHT JOIN between functions returning refcursor type. No. Make them return setof whatever instead. I would like yo avoid creating custom compo

Re: [SQL] JOIN results of refcursor functions

Milan Oparnica escribió: > Tom Lane wrote: >> Milan Oparnica <[EMAIL PROTECTED]> writes: >>> Is there any way to use INNER, LEFT and RIGHT JOIN between functions >>> returning refcursor type. >> >> No. Make them return setof whatever instead. >> > I would like yo avoid creating custom composite

Re: [SQL] JOIN results of refcursor functions

Tom Lane wrote: Milan Oparnica <[EMAIL PROTECTED]> writes: Is there any way to use INNER, LEFT and RIGHT JOIN between functions returning refcursor type. No. Make them return setof whatever instead. regards, tom lane I would like yo avoid creating custom composite t

Re: [SQL] JOIN results of refcursor functions

Milan Oparnica <[EMAIL PROTECTED]> writes: > Is there any way to use INNER, LEFT and RIGHT JOIN between functions > returning refcursor type. No. Make them return setof whatever instead. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To

Re: [SQL] Join question

PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Edward W. Rouse Sent: Tuesday, August 19, 2008 2:04 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Join question I was trying to do something like this, but couldn't get it to work. I am trying to follow the example you provided, but

Re: [SQL] Join question

EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lennin Caro Sent: Tuesday, August 19, 2008 11:59 AM To: 'Daniel Hernandez'; pgsql-sql@postgresql.org; Edward W. Rouse Subject: Re: [SQL] Join question --- On Tue, 8/19/08, Edward W. Rouse <[EMAIL PROTECTED]> wrote:

Re: [SQL] Join question

--- On Tue, 8/19/08, Edward W. Rouse <[EMAIL PROTECTED]> wrote: > From: Edward W. Rouse <[EMAIL PROTECTED]> > Subject: Re: [SQL] Join question > To: "'Daniel Hernandez'" <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org > Date: Tuesday, August 19,

Re: [SQL] Join question

To: pgsql-sql@postgresql.org Sent: Tuesday, August 19, 2008 2:36 PM Subject: Re: [SQL] Join question I thought of that, but it does violate table constraints. Edward W. Rouse From: Oliveiros Cristina [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2008 2:00 PM To:

Re: [SQL] Join question

I have tried left, right outer and inner. Edward W. Rouse From: Daniel Hernandez [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2008 12:30 PM To: pgsql-sql@postgresql.org; [EMAIL PROTECTED] Subject: Re: [SQL] Join question have you tried a right Join? Daniel Hernndez. San Diego

Re: [SQL] Join question

I thought of that, but it does violate table constraints. Edward W. Rouse From: Oliveiros Cristina [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2008 2:00 PM To: pgsql-sql@postgresql.org; [EMAIL PROTECTED] Subject: Re: [SQL] Join question I don't understand your count(

Re: [SQL] Join question

Original Message - From: Daniel Hernandez To: pgsql-sql@postgresql.org ; [EMAIL PROTECTED] Sent: Monday, August 18, 2008 5:30 PM Subject: Re: [SQL] Join question have you tried a right Join? Daniel Hernndez. San Diego, CA. "The more you learn, the more you earn&qu

Re: [SQL] Join question

have you tried a right Join?Daniel Hernndez.San Diego, CA."The more you learn, the more you earn".Fax: (808) 442-0427-Original Message-From: "Edward W. Rouse" [EMAIL PROTECTED]: 08/15/2008 09:48 AMTo: [EMAIL PROTECTED]: Re: [SQL] Join question I have 2

Re: [SQL] Join question

At 12:20 PM 8/15/2008, [EMAIL PROTECTED] wrote: Date: Fri, 15 Aug 2008 13:46:14 -0400 From: "Edward W. Rouse" <[EMAIL PROTECTED]> To: Subject: Re: Join question Message-ID: <[EMAIL PROTECTED]> I did try that, but I can't get both the values from table a with no entries in table b and the value

Re: [SQL] Join question

Of Richard Broersma Sent: Friday, August 15, 2008 1:10 PM To: Edward W. Rouse Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Join question On Fri, Aug 15, 2008 at 9:48 AM, Edward W. Rouse <[EMAIL PROTECTED]> wrote: > The problem is I also have to include > items from table b with that have

Re: [SQL] Join question

On Fri, Aug 15, 2008 at 9:48 AM, Edward W. Rouse <[EMAIL PROTECTED]> wrote: > The problem is I also have to include > items from table b with that have a null user. There are some other criteria > as well that are simple where clause filters. So as an example: instead of left join try FULL OUTER

Re: [SQL] Join question

Sigh, I messed up the tables a bit when I typed the example, org A was supposed to have entries for all 3 users in table a just like org B does, not just the one. Sorry for the confusion. Edward W. Rouse From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Edward W. Rouse Sent:

Re: [SQL] JOIN a table twice for different values in the same query

Magne Mæhre wrote: Colin Wetherbee wrote: Colin Wetherbee wrote: Phillip Smith wrote: As a side note - all the IATA codes are unique for each airport - wouldn't it be better to use these as the Primary Key and Foreign Keys? Then you wouldn't have to even join the tables unless you wanted the p

Re: [SQL] JOIN a table twice for different values in the same query

Colin Wetherbee wrote: Colin Wetherbee wrote: Phillip Smith wrote: As a side note - all the IATA codes are unique for each airport - wouldn't it be better to use these as the Primary Key and Foreign Keys? Then you wouldn't have to even join the tables unless you wanted the port names (not just

Re: [SQL] JOIN a table twice for different values in the same query

Colin Wetherbee wrote: Phillip Smith wrote: As a side note - all the IATA codes are unique for each airport - wouldn't it be better to use these as the Primary Key and Foreign Keys? Then you wouldn't have to even join the tables unless you wanted the port names (not just the code) This is true

Re: [SQL] JOIN a table twice for different values in the same query

Phillip Smith wrote: As a side note - all the IATA codes are unique for each airport - wouldn't it be better to use these as the Primary Key and Foreign Keys? Then you wouldn't have to even join the tables unless you wanted the port names (not just the code) This is true, but FWIW, my applicati

Re: [SQL] JOIN a table twice for different values in the same query

Try something like this where we alias the joined tables: SELECT departure_date, j1.code AS departure_code, j2.code AS arrival_code FROM jsjourneys LEFT OUTER JOIN jsports AS j1 ON jsjourneys.departure_port = j1.id LEFT OUTER JOIN jsports AS j2 ON jsjourneys.arrival_port = j2.id;

Re: [SQL] JOIN a table twice for different values in the same query

Paul Lambert wrote: Colin Wetherbee wrote: I would like to construct a query on the flight table that returns the names of both the departure port and the arrival port. The following query shows how I would get just the departure port. js=# SELECT departure_date, jsports.code AS departure_cod

Re: [SQL] JOIN a table twice for different values in the same query

js=# SELECT departure_date, departure.code AS departure_code, arrival.code as arraival_codeFROM jsjourneys         JOIN jsports as departure ON jsjourneys.departure_port = departure.id        JOIN jsports as arrival on jsjourneys.arraival_port = arraival.id LIMIT4; Regards,Daniel Hernández.San

Re: [SQL] JOIN a table twice for different values in the same query

Colin Wetherbee wrote: Greetings. I have two tables I'm having a little trouble figuring out how to JOIN. One contains a list of airports along with their IATA codes, cities, names, and so forth. This table also contains an id column, which is a serial primary key. The other table contains

Re: [SQL] join on three tables is slow

Tom Lane wrote: Gerry Reno <[EMAIL PROTECTED]> writes: Pavel Stehule wrote: there is diference in agg position. Send, please, query and explain analyze output. [ explain analyze output ] The rowcount estimates seem pretty far off, even for simple cases that I'd expect it to ge

Re: [SQL] join on three tables is slow

Gerry Reno <[EMAIL PROTECTED]> writes: > Pavel Stehule wrote: >> there is diference in agg position. Send, please, query and explain >> analyze output. [ explain analyze output ] The rowcount estimates seem pretty far off, even for simple cases that I'd expect it to get right, eg > ->

Re: [SQL] join on three tables is slow

Hello 1) increase statistics on res_partner_address.type (about 100) alter table ... ALTER [ COLUMN ] column SET STATISTICS integer do analyze, and look again on plans. There are difference -> Seq Scan on res_partner_address a (cost=0.00..88.40 rows=16 width=552) (actual time=0.106..3.

Re: [SQL] join on three tables is slow

Pavel Stehule wrote: Hello there is diference in agg position. Send, please, query and explain analyze output. And test id = any (... ) looks like hard denormalisation and can do problems. This condition can be slow and for large arrays is better use multivalues. SELECT * FROM tab WHERE x

Re: [SQL] join on three tables is slow

Hello there is diference in agg position. Send, please, query and explain analyze output. And test id = any (... ) looks like hard denormalisation and can do problems. This condition can be slow and for large arrays is better use multivalues. SELECT * FROM tab WHERE x IN (VALUES(10),(20));

Re: [SQL] join on three tables is slow

Pavel Stehule wrote: On 11/12/2007, Gerry Reno <[EMAIL PROTECTED]> wrote: Ok, I've been playing around with this SQL some more and I found that if I remove this: e.active = '1' from the query that the query now completes in 5 seconds. Nothing else has anywhere near the impact of this boolean

Re: [SQL] join on three tables is slow

On 11/12/2007, Gerry Reno <[EMAIL PROTECTED]> wrote: > Ok, I've been playing around with this SQL some more and I found that if > I remove this: > e.active = '1' > from the query that the query now completes in 5 seconds. Nothing else > has anywhere near the impact of this boolean condition. So w

Re: [SQL] join on three tables is slow

Ok, I've been playing around with this SQL some more and I found that if I remove this: e.active = '1' from the query that the query now completes in 5 seconds. Nothing else has anywhere near the impact of this boolean condition. So what is it about this boolean field that is causing so much

Re: [SQL] join on three tables is slow

Gerry Reno wrote: I have a join that uses three tables but it runs rather slow. For example, the following command takes about 10 min. to run. It gets the correct result but what should I do to increase the performance of this query? This query is the end result of some python code hence the

Re: [SQL] join on three tables is slow

Here is the query plan: QUERYPLAN

Re: [SQL] Join question

On Aug 21, 2007, at 12:48 , [EMAIL PROTECTED] wrote: SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id = c.b_id GROUP by a.x; Shouldn't affect performance, but another way to write this which you may find more readable is to list your join conditions with the joins rather

Re: [SQL] Join question

oops... I meant "DISTINCT ON ( a_id )" --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > SELECT a.x, b.x > FROM ( SELECT DISTINCT ON ( a_id ) a_id, b_id ^^ >FROM c ) AS c( a_id, b_id ) > INNER JOIN a > ON c.a_id = a.id > INNER J

Re: [SQL] Join question

--- [EMAIL PROTECTED] wrote: > create table c ( >a_id int, >b_id int > ); > > I am doing a query like this: > > SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id = > c.b_id GROUP by a.x; > > I only need to get one row from b for each row in a, and it really > doesn't ma

Re: [SQL] Join query help

On 21/08/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: > Now here's where I started having trouble. I can't figure out how to > get 2 observations for week 29 (record_id 1 & 3) and 8 for week 30 > (record_id 2). Assuming the data is wrong (which is admittedly a poor > assumption), I moved ahea

Re: [SQL] Join query help

On Aug 18, 2007, at 0:35 , novice wrote: What query do I write to generate the following? week_no | count(record_id | count(observation_id) | sum(score_id) where = '1' 2007, 30 | 2 | 8 | 6 2007, 29 | 1 | 2 | 1 Okay: let's take a look at what you're trying to get: fi

Re: [SQL] Join question

Phillip Smith wrote: Whoops, I forgot the JOIN conditions! Fixed below -Original Message- From: Phillip Smith [mailto:[EMAIL PROTECTED] Sent: Friday, 27 July 2007 11:47 To: 'pgsql-sql@postgresql.org' Subject: RE: [SQL] Join question This might give you a starting

Re: [SQL] Join question

Whoops, I forgot the JOIN conditions! Fixed below -Original Message- From: Phillip Smith [mailto:[EMAIL PROTECTED] Sent: Friday, 27 July 2007 11:47 To: 'pgsql-sql@postgresql.org' Subject: RE: [SQL] Join question This might give you a starting point if I understand you

Re: [SQL] Join question

This might give you a starting point if I understand you correctly... SELECT h.invoice_number, h.customer, l.item, l.amount FROMlines AS l JOINheaders AS h UNION SELECT h.invoice_number, h.customer, s.item, s.amount FROMsundries AS

Re: [SQL] join problem

On sun, 2007-06-24 at 06:14 -0500, A. R. Van Hook wrote: [ in the future, please avoid top-posting, as it is annoying to have to rearrange lines when replying ] > Ragnar wrote: > > On lau, 2007-06-23 at 04:15 -0500, A. R. Van Hook wrote: > >> Ragnar wrote: > >>> On fim, 2007-06-21 at 08:46 -0500

Re: [SQL] join problem

Works great. Can you enlighten me as why the deposit is divided by the number of rows? thanks Ragnar wrote: On lau, 2007-06-23 at 04:15 -0500, A. R. Van Hook wrote: Ragnar wrote: On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote: If I try an inclusive query using the fol

Re: [SQL] join problem

On lau, 2007-06-23 at 04:15 -0500, A. R. Van Hook wrote: > Ragnar wrote: > > On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote: > >> If I try an inclusive query using the following: > >> select > >> sum(i.rowtot + i.tax) as tot, > >> sum(v.deposit) as deposit > >> from ca

Re: [SQL] join problem

What is the correct query??? thanks Ragnar wrote: On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote: if I query for the total deposit using select sum(deposit) from invoice where cusid = 2128" I also get 1179.24, also the correct amount If I try an inclusive query using th

Re: [SQL] join problem

On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote: > if I query for the total deposit using >select sum(deposit) >from invoice >where cusid = 2128" > > I also get 1179.24, also the correct amount > > > If I try an inclusive query using the following: > select > sum(i

Re: [SQL] join problem

[Please don't top post as it makes the discussion more difficult to follow, and please reply to the list so that others may benefit from and participate in the discussion.] On Jun 19, 2007, at 14:17 , A. R. Van Hook wrote: Michael Glaesemann wrote: On Jun 13, 2007, at 8:19 , A. R. Van Ho

Re: [SQL] join problem

There is not referenced table cai in from clausule. A. R. Van Hook napsal(a): > I have join problem: > "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, > sum(i.tax) as tax, > sum(i.tax + i.rowtot) as totalP, > (sum(i.tax + i.rowtot) - v.dep

Re: [SQL] join problem

On Jun 13, 2007, at 8:19 , A. R. Van Hook wrote: I have join problem: "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, sum(i.tax) as tax, sum(i.tax + i.rowtot) as totalP, (sum(i.tax + i.rowtot) - v.deposit) as balance from

Re: [SQL] join problem

You can do it like this: select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, sum(i.tax) as tax, sum(i.tax + i.rowtot) as totalP, (sum(i.tax + i.rowtot) - v.deposit) as balance from invoice v join cai on v.cusid = cai

Re: [SQL] join problem

A. R. Van Hook a écrit : > I have join problem: > "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, > sum(i.tax) as tax, > sum(i.tax + i.rowtot) as totalP, > (sum(i.tax + i.rowtot) - v.deposit) as balance > from invoice v >

Re: [SQL] JOIN

I forgot the attachement :) On 6/6/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote: I found my problem. I attaches an .sql file with my tables, data and my new query. If you are interested you can take a look. Thanks to all. Regards, Loredana CREATE TABLE reminder_services ( uid S

Re: [SQL] JOIN

I found my problem. I attaches an .sql file with my tables, data and my new query. If you are interested you can take a look. Thanks to all. Regards, Loredana

Re: [SQL] JOIN

Oliveiros, I think it's time to give some more details about my task. I will start with the begining :) I have a "log" table which stores the dates when users send messages with a theme from their mobile phone. This table is named sent_messages and looks like this: receiver | theme

Re: [SQL] JOIN

ay, June 05, 2007 3:46 PM Subject: Re: [SQL] JOIN Hmm...try to add the following clause to your INNER JOIN AND A.date = B.Date Like this : INNER JOIN view_sent_messages B ON A.theme=B.theme AND A.receiver=B.receiver AND A.date = b

Re: [SQL] JOIN

On 6/5/07, Oliveiros Cristina <[EMAIL PROTECTED]> wrote: Hello again, Loredana. Additional information required :-) imagine the following situation 1| LIA | recv1 | date1 | (date1,date2) 2|LIA | recv1 |date 1 | (date2,date3) 3| LIA | recv1 | date1 | (date1,date3) Should this yield 6? Or 4

Re: [SQL] JOIN

remaining two Cheers, Oliveiros - Original Message - From: Loredana Curugiu To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] ; pgsql-sql@postgresql.org Sent: Tuesday, June 05, 2007 3:15 PM Subject: Re: [SQL] JOIN On 6/5/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote:

Re: [SQL] JOIN

Hmm...try to add the following clause to your INNER JOIN AND A.date = B.Date Like this : INNER JOIN view_sent_messages B ON A.theme=B.theme AND A.receiver=B.receiver AND A.date = b.Date AND B.date=ANY (A.dates) Doesn't work. I get the result

Re: [SQL] JOIN

Hey, Loredana. Nice to "see" you too ;-) Thank you for your detailed clarifications. Hmm...try to add the following clause to your INNER JOIN AND A.date = B.Date Like this : INNER JOIN view_sent_messages B ON A.theme=B.theme AND A.receiver=B.receiver AND A.d

Re: [SQL] JOIN

On 6/5/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote: On 6/5/07, Oliveiros Cristina <[EMAIL PROTECTED]> wrote: > > Hey, Loredana. Hi Oliveiros! Nice to "see" you again! Please advice me, > you need to sum for a certain pair (Theme, receiver) the number that > appears on count for every rec

Re: [SQL] JOIN

On 6/5/07, Oliveiros Cristina <[EMAIL PROTECTED]> wrote: Hey, Loredana. Hi Oliveiros! Nice to "see" you again! Please advice me, you need to sum for a certain pair (Theme, receiver) the number that appears on count for every record whose date is in dates column, is this correct? Yap. Bu

Re: [SQL] JOIN

Loredana Curugiu wrote: You don't actually say what's wrong. What are you expecting as output? I should obtain the following result: [snip] Well, I've attached a test script using your example data and a copy of my results. Nothing leaping out as wrong here. It's entirely possible I've not

Re: [SQL] JOIN

You don't actually say what's wrong. What are you expecting as output? I should obtain the following result: sum | theme | receiver | dates +--+--+ 8 | CRIS | +4074

Re: [SQL] JOIN

Hey, Loredana. Please advice me, you need to sum for a certain pair (Theme, receiver) the number that appears on count for every record whose date is in dates column, is this correct? But in what record's dates column? On all of them? Or just the dates column of the records that have that (Theme

Re: [SQL] JOIN

"Loredana Curugiu" <[EMAIL PROTECTED]> writes: > I am trying to say that sum column it is not calculated correctly. It looks like your counts are high. That would imply that your join clauses are matching more than one combination of rows. Ie, it's counting some rows multiple times because there

Re: [SQL] JOIN

Loredana Curugiu wrote: So I have the following query:>> >>SELECT SUM(A.count), >> A.theme, >> A.receiver, >> A.dates >> FROM my_table A >> INNER JOIN my_table B >> ON A.theme=B.theme >>

Re: [SQL] JOIN

So I have the following query:>> >>SELECT SUM(A.count), >> A.theme, >> A.receiver, >> A.dates >> FROM my_table A >> INNER JOIN my_table B >> ON A.theme=B.theme >> AND A.receiver=B.receive

Re: [SQL] JOIN

Loredana Curugiu wrote: Any help, please? So I have the following query: SELECT SUM(A.count), A.theme, A.receiver, A.dates FROM my_table A INNER JOIN my_table B ON A.theme=B.theme AND A

Re: [SQL] JOIN

Any help, please? On 6/5/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote: Hi everybody, I have the following table: count | theme | receiver| date | dates | ---+---+---

Re: [SQL] join table with itself?

William Leite Araújo wrote: On 15/03/07, *T E Schmitz* <[EMAIL PROTECTED] > wrote: (...) Try join the tables. SELECT present.day, present.low, (MIN(future.day)-present.day) as days2fall FROM history AS present JOIN history AS future ON ( present.day

Re: [SQL] join table with itself?

On 15/03/07, T E Schmitz <[EMAIL PROTECTED]> wrote:(...) Try join the tables. SELECT present.day, present.low, (MIN(future.day)-present.day) as days2fall FROM history AS present JOIN history AS future ON ( present.day < future.day AND future.low <= present.low ) GROUP BY present.day,

Re: [SQL] join/group/count query.

On Wednesday 20 December 2006 18:06, Hector Villarreal wrote: > HI in reading this is it possible what was really intended : > Sum(case when o_model = 5 then 1 else NULL end) as KA > That would provide a count of all records meeting that condition. > Otherwise the count( approach will not do that.

  1   2   3   >