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
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 gives back a table with all userright_ids this user
di
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
When I join tables; I will sometimes get multiple rows back as in this
example.
create table class(name varchar, p point, d int);
insert into class values( 'All', point(50,50), 100 );
insert into class values( 'NE70', point(70,70), 20 );
insert into class values( 'NE75', point(75,75), 20 );
inse
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
I have a specific problem with a join table and I've simplified it to
these 3 tables so as not to have to post anything that hints towards
the business nature of our database just in case ..
At any rate, say I have 3 tables:
table plant
id:integer
name string
table seed_supplier
id: in
Στις 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
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
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
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(t1.id) without success.
Thanks for any suggestions,
--
S
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
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
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
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
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
---++--
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
---++---
user
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
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
i'm in need of some psql advise,
believe its rather a trivial issue, but confusing for me...
facing following issue:
got 2 tables like:
CREATE TABLE td_fetch_by_rrd_id (
f_rrd_id numeric NOT NULL,
f_timestamp numeric NOT NULL,
f_ds numeric,
f_us numeric,
CONSTRAINT td_fetch_by_rrd_id_p
I'm trying to get rid of the side effect of the
left join catalog_promosimple
That means I'm expecting at least one row no matter if there is an
entry in
left join catalog_promosimpleitem
but I don't want "duplicates" from
catalog_promosimple
create table catalog_promosimple (
PromoSimpleID in
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
> >
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
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
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.
> >
> >
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,
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),
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,
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
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
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
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, COUNT
COUNT is the number of matching D,S,C co
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
>
> 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
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
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
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
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
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
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
Hi,
Is there any way to use INNER, LEFT and RIGHT JOIN between functions
returning refcursor type.
Explain:
function1 as refcursor
function2 as refcursor
both functions return columns a and b.
can i join the results of these functions in such manner (or any other):
function3 as refcursor
Hello, list. First post here, I think.
So I've worked with several *lousy* DB engines and *poorly* designed databases
behind crappy applications over these many years. I look forward to moving
everything to PG. First issue is addressed.
Now with the grand opportunity to be hoist upon my own pet
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
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:
--- 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,
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:
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
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(
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
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
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
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
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
: Friday, August 15, 2008 12:48 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Join question
I have 2 tables, both have a user column. I am currently using a left join
from table a to table b because I need to show all users from table a even
those not having an entry in table b. The problem is
I have 2 tables, both have a user column. I am currently using a left join
from table a to table b because I need to show all users from table a even
those not having an entry in table b. The problem is I also have to include
items from table b with that have a null user. There are some other crite
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
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
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
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
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;
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
Hernández.San Diego, CA."The more you learn, more you
earn". --- On Thu 01/10, Colin Wetherbee < [EMAIL PROTECTED] >
wrote:From: Colin Wetherbee [mailto: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Thu,
10 Jan 2008 17:07:00 -0500Subject: [SQL] JOIN a table twice for different
values in the same
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
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 a list of flights, each
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
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
> ->
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.
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
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));
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
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
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
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
Here is the query plan:
QUERYPLAN
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 big
id list.
my
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
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
--- [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
Hey All,
I have a query I'm trying to speed up, and I was hoping someone could
help me. I have a three tables a and b hold data, and c just references
between a and b:
create table a (
a_id int,
x int
);
create table b (
b_id int,
x int
);
create table c (
a_id int,
b_id int
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
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
Hi, We have the following three tables.
safety=> SELECT record_id, record_date FROM record;
record_id | record_date
---+
1 | 2007-07-23 11:30:37+10
2 | 2007-07-27 11:30:14+10
3 | 2007-07-17 13:15:03+10
(3 rows)
safety=> SELECT obser
Hi!
I've been trying to optimize a query in which I join several tables, since
I've seen it takes about 2 seconds, which is way too much.
Well, the query is the following, I'm using LEFT OUTER JOIN just when the
tables can have NULL results, plain JOIN otherwise:
select ="select to_char(a.fecha_
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
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
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
I have a database in a parts sales environment that I am having a little
difficulty with a join query - trying to figure out which way to join
things.
I have a parts_invoice_header table, containing the header record for
each invoice.
I have a parts_invoice_lines table, containing the parts de
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
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
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
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
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
I have three tables relating to purchases
invoice - transaction data (customer id, deposit. etc)
invoiceitems - purachace items detail
cai - customer data
if I query for the total charges using
select sum(rowtot + tax)
from invoiceitems
where ivid in (select ivi
[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
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
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
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
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
>
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
left outer 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
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
1 - 100 of 290 matches
Mail list logo
