Re: HELP: I am desperate(view problems)

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1997/12/01
Message-ID: <65uf8v$2i9$1@news02.btx.dtag.de>#1/1

Casper Thrane wrote:
>
> Hi!
>
> I have the following problem.
>
> I have three tables which looks like this:
>
> account:
> actid
> ..
> ..
> ..
> / \
> / \
> / \
> usage: budget:
> actid actid
> transdate transdate
> amount amount
>
> In this model there is a budget transaction the 1st in every month.
>
> Now I want to create a view which should look like this:
>
> view:
> actid transdate usageamount budgetamount
>
> example
> account:
> actid 1014
>
> usage:
> actid 1014
> transdate 19970303
> amount 800
>
> actid 1014
> transdate 19970305
> amount 800
>
> budget:
> actid 1014
> transdate 19970103
> amount 1900
>
> the view
> actid transdate usage budget
> 1014 19970101 0 1900
> 1014 19970303 800 0
> 1014 19970305 800 0
>
> If there isn't any usage or a any budget for a specific date, there has
> to be a zero.
>
> My problem is, what ever I do, I end up with the Cartesian product.
>
> HEEEELLLLPPPPPPP!!!!!!!
> --
> Casper Thrane
> System developer
> Benau A/S

Hi Casper,

try something like:

Select

	actid ,
	transdate ,
	amount usage,
	0 budget
from
	usage

union all
Select

	actid ,
	transdate ,
	0 usage,
	amount budget
from
	budget

order by 1,2

-- 
Regards

Matthias Gresz    :-)

Received on Mon Dec 01 1997 - 00:00:00 CST