Re: newbee question

On Fri, 23 Jul 2004 17:38:04 +0200, "André Hartmann" <andrehartmann_at_hotmail.com> wrote:

>> The question:
>>
>> How do I make my query to end up with
>>
>> P_KEY1 P_KEY2 DATA_2_1 P_KEY3 DATA_3_1
>> key_1 key_2.1 data21.1 key_3.1 data31.1
>> key_1 key_2.2 data21.2 key_3.2 data31.2
>>
>> instead?
>
> In your current design there seems no link between tables 2 and 3. Since
>in your statement you join all the 3 tables you end up getting the product
>of tables 2 and 3 there (each line from table 2 combined with each line of
>table 3).
>
> If thats not what you want then you obviously have a relationship in mind
>between the rows in tables 2 and those in 3. This relationship is not
>modelled in the database. Maybe table 3 needs a foreign key to table 2 ? or
>the other way round ? you need to investigate the nature of the data here,
>especially why you would give "key_1 key_2.1 data21.1 key_3.1 data31.1"
>preference over "key_1 key_2.1 data21.1 key_3.2 data31.2" in your expected
>result.
>
> Once you have a foreign key in table 2 (or 3) towards table 3 (or 2) you
>just add one more condition to your where clause linking the two tables and
>you should get the expected result.
>
>

I just put an exemple to clarify the thing

Table 1 contain invoices
Table 2 contain which item are invoiced (we can invoice more than one item per invoice)
Table 3 contain which account are affected by the invoice (once more there can be more than one account affected per invoice)

But: there is no relation between item (table 2) and account (table 3) since one item may affect multiple account and multiple item may affect the same account. Table 3 is the summary of the affected account for an invoice.

What I want is for every invoice the list of item and the list of account and since I do sum of amount, every time that some are repeted the sum is not correct Received on Fri Jul 23 2004 - 10:53:13 CDT