Re: simple yet complex join

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