Обсуждение: simple yet complex join
Hello All, I've been reading the archives, the manual and my sql books. lack of sleep is making what seems easy very hard to figure out right now. what kind of join do I have to do in order to combine 3 tables of similiar information. For example 3 months' worth of payments from customers: jan_pay tom 25 ron 30 jim 0 feb_pay tom 25 ron 20 jim 10 march_pay tom 25 ron 30 jim 5 pat 40 I want a result that looks like this : tom 25 25 25 ron 30 20 30 jim 0 10 5 pat 0 0 40 I've tried so many kinds of strage joins that I am ashamed to post them here. Can someone please light the candle? Thanks in advance. -- Vincent Stoessel Linux Systems Developer vincent xaymaca.com
On Thu, 16 May 2002, Vincent Stoessel wrote: > Hello All, > I've been reading the archives, the manual and my sql books. > lack of sleep is making what seems easy very hard to figure out > right now. > > what kind of join do I have to do in order to combine 3 tables of > similiar information. For example 3 months' worth of payments from > customers: > > > jan_pay > > > tom 25 > ron 30 > jim 0 > > > feb_pay > > tom 25 > ron 20 > jim 10 > > > march_pay > > tom 25 > ron 30 > jim 5 > pat 40 > > > > I want a result that looks like this : > > > tom 25 25 25 > ron 30 20 30 > jim 0 10 5 > pat 0 0 40 > > > > I've tried so many kinds of strage joins that I am ashamed to post them > here. Can someone please light the candle? > Thanks in advance. A simple cross join? SELECT jan.name AS name ,jan.pay AS jan_pay ,feb.pay AS feb_pay ,mar.pay AS mar_pay FROM jan_pay jan, feb_pay feb, mar_pay mar WHERE jan.name = feb.name AND feb.name = mar.name ; Does that work? I've got to say though, it looks an odd arrangement to have. I know you can't get your person x month table output without some other coding but wouldn't a more traditional database design have a single table something like: table : pay columns : name pay month ? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants
I think what you are looking for is a "pivot table"? This is not easy using SQL, but workarounds have been discussed several times in the past. I suggest you search the archives for "pivot table", and you will find plenty of references. --- Vincent Stoessel <vincent@xaymaca.com> wrote: > Hello All, > I've been reading the archives, the manual and my > sql books. > lack of sleep is making what seems easy very hard to > figure out > right now. > > what kind of join do I have to do in order to > combine 3 tables of > similiar information. For example 3 months' worth of > payments from > customers: > > > jan_pay > > > tom 25 > ron 30 > jim 0 > > > feb_pay > > tom 25 > ron 20 > jim 10 > > > march_pay > > tom 25 > ron 30 > jim 5 > pat 40 > > > > I want a result that looks like this : > > > tom 25 25 25 > ron 30 20 30 > jim 0 10 5 > pat 0 0 40 > > > > I've tried so many kinds of strage joins that I am > ashamed to post them > here. Can someone please light the candle? > Thanks in advance. > > > -- > Vincent Stoessel > Linux Systems Developer > vincent xaymaca.com > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster __________________________________________________ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com
Hmmm, thanks for the pointer. I think I'm going to take the easier road and program my way through the problem with some perl iterations. Thanks all! Jeff Eckermann wrote: > I think what you are looking for is a "pivot table"? > This is not easy using SQL, but workarounds have been > discussed several times in the past. I suggest you > search the archives for "pivot table", and you will > find plenty of references. > > --- Vincent Stoessel <vincent@xaymaca.com> wrote: > >>Hello All, >>I've been reading the archives, the manual and my >>sql books. >>lack of sleep is making what seems easy very hard to >>figure out >>right now. >> >>what kind of join do I have to do in order to >>combine 3 tables of >>similiar information. For example 3 months' worth of >>payments from >>customers: >> >> >>jan_pay >> >> >>tom 25 >>ron 30 >>jim 0 >> >> >>feb_pay >> >>tom 25 >>ron 20 >>jim 10 >> >> >>march_pay >> >>tom 25 >>ron 30 >>jim 5 >>pat 40 >> >> >> >>I want a result that looks like this : >> >> >>tom 25 25 25 >>ron 30 20 30 >>jim 0 10 5 >>pat 0 0 40 >> >> >> >>I've tried so many kinds of strage joins that I am >>ashamed to post them >>here. Can someone please light the candle? >>Thanks in advance. >> >> >>-- >>Vincent Stoessel >>Linux Systems Developer >>vincent xaymaca.com >> >> >>---------------------------(end of >>broadcast)--------------------------- >>TIP 4: Don't 'kill -9' the postmaster > > > > __________________________________________________ > Do You Yahoo!? > LAUNCH - Your Yahoo! Music Experience > http://launch.yahoo.com -- Vincent Stoessel Linux Systems Developer vincent xaymaca.com
Why you doing it in perl you can easily do it in SQL
look at the following
The tables that you had
CREATE TABLE jan_pay (
name VARCHAR(32) NOT NULL,
value INTEGER NOT NULL
)
;
INSERT INTO jan_pay VALUES ('Tom',20);
INSERT INTO jan_pay VALUES ('John',90);
INSERT INTO jan_pay VALUES ('Mike',65);
CREATE TABLE feb_pay (
name VARCHAR(32) NOT NULL,
value INTEGER NOT NULL
)
;
INSERT INTO feb_pay VALUES ('Tom',25);
INSERT INTO feb_pay VALUES ('John',30);
INSERT INTO feb_pay VALUES ('Mike',20);
CREATE TABLE mar_pay (
name VARCHAR(32) NOT NULL,
value INTEGER NOT NULL
)
;
INSERT INTO mar_pay VALUES ('Tom',23);
INSERT INTO mar_pay VALUES ('John',43);
INSERT INTO mar_pay VALUES ('Mike',56);
Query Run:
SELECT a.name,a.value,b.value,c.value
FROM jan_pay a,feb_pay b,mar_pay c
WHERE a.name = b.name AND
a.name = c.name;
Result:
name | value | value | value
------+-------+-------+-------
John | 90 | 30 | 43
Mike | 65 | 20 | 56
Tom | 20 | 25 | 23
HTH
Darren Ferguson
On Fri, 17 May 2002, Vincent Stoessel wrote:
> Hmmm, thanks for the pointer.
> I think I'm going to take the easier road and program
> my way through the problem with some perl iterations.
>
> Thanks all!
>
>
>
> Jeff Eckermann wrote:
> > I think what you are looking for is a "pivot table"?
> > This is not easy using SQL, but workarounds have been
> > discussed several times in the past. I suggest you
> > search the archives for "pivot table", and you will
> > find plenty of references.
> >
> > --- Vincent Stoessel <vincent@xaymaca.com> wrote:
> >
> >>Hello All,
> >>I've been reading the archives, the manual and my
> >>sql books.
> >>lack of sleep is making what seems easy very hard to
> >>figure out
> >>right now.
> >>
> >>what kind of join do I have to do in order to
> >>combine 3 tables of
> >>similiar information. For example 3 months' worth of
> >>payments from
> >>customers:
> >>
> >>
> >>jan_pay
> >>
> >>
> >>tom 25
> >>ron 30
> >>jim 0
> >>
> >>
> >>feb_pay
> >>
> >>tom 25
> >>ron 20
> >>jim 10
> >>
> >>
> >>march_pay
> >>
> >>tom 25
> >>ron 30
> >>jim 5
> >>pat 40
> >>
> >>
> >>
> >>I want a result that looks like this :
> >>
> >>
> >>tom 25 25 25
> >>ron 30 20 30
> >>jim 0 10 5
> >>pat 0 0 40
> >>
> >>
> >>
> >>I've tried so many kinds of strage joins that I am
> >>ashamed to post them
> >>here. Can someone please light the candle?
> >>Thanks in advance.
> >>
> >>
> >>--
> >>Vincent Stoessel
> >>Linux Systems Developer
> >>vincent xaymaca.com
> >>
> >>
> >>---------------------------(end of
> >>broadcast)---------------------------
> >>TIP 4: Don't 'kill -9' the postmaster
> >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > LAUNCH - Your Yahoo! Music Experience
> > http://launch.yahoo.com
>
>
>
> --
> Vincent Stoessel
> Linux Systems Developer
> vincent xaymaca.com
>
>
> ---------------------------(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
>