Re: simple yet complex join

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
>