I have two tables:

* One of them saves the list of sales, for example:

NUMBER    DATE                TOTAL
   1               2008-10-01        50
   2               2008-10-03        25
   3               2008-10-03        50

* Another table stores the payments methods of each sale (if any), for
example

NUMBER    DATE                TOTAL    PAYMENT-METHOD
   2               2008-10-03        10            CREDIT CARD
   2               2008-10-05        15            BANK TRANSFER



I would like to show ALL data in the following manner:

NUMBER    DATE                TOTAL    PAYMENTS
   1                2008-10-01        50                0
   2                2008-10-03        25                25
   3                2008-10-03        50                0


If it is NOT possible, I would like to show
a) only the records from table1 (sales) with no payments in table2
b) only the records from table1 (sales) with payments in table2

Is it possible?

Thanks!

Santy

> SALES_TABLE
> NUMBER    DATE                TOTAL
>    1               2008-10-01        50
>    2               2008-10-03        25
>    3               2008-10-03        50
>
> PAYMENT_TABLE
> NUMBER    DATE                TOTAL    PAYMENT-METHOD
>    2               2008-10-03        10            CREDIT CARD
>    2               2008-10-05        15            BANK TRANSFER
>
> I would like to show ALL data in the following manner:
>
> NUMBER    DATE                TOTAL    PAYMENTS
>    1                2008-10-01        50                0
>    2                2008-10-03        25                25
>    3                2008-10-03        50                0

select number, sales_table.date, sales_table.total, sum(total)
from payment_table
inner join sales_table on (payment_table.number = sales_table.number)
group by number

Eduardo

Thanks for your answer, but I'm afraid I was wrong with my explanation (or
maybe I misunderstood what I really needed).

Actually, sales table has its own payment method too, so the problem is a
little bit difficult.
Some sales are payed with a UNIQUE payment method (not saved in payments
table), but another ones are payed with two (or more) payments methods
(saves in payments table)

So we have:

SALES_TABLE
NUMBER    DATE                TOTAL    PAYMENT-METHOD
   1               2008-10-01        50        CASH
   2               2008-10-03        25        {SEVERAL METHODS}
   3               2008-10-03        50        CREDIT CARD

PAYMENT_TABLE
NUMBER    DATE                TOTAL    PAYMENT-METHOD
   2               2008-10-03        10            CREDIT CARD
   2               2008-10-05        15            BANK TRANSFER


I need to show all sales WITH UNIQUE payments methods, and all sales WITH
SEVERAL payments methods.
Don't matter if it is shown in one simple query or separate ones.

For example (if possible with one simple query)

NUMBER    DATE                TOTAL    PAYMENTS    METHOD
   1                2008-10-01        50                0              CASH
   2                2008-10-03        25                10
CREDIT CARD
   2                2008-10-05        25                15            BANK
TRANSFER
   3                2008-10-03        50                0
CREDIT CARD

BUT! If it is better to get it with two different queries, no problem:

Show all sales with UNIQUE payments:

NUMBER    DATE                TOTAL    METHOD
   1                2008-10-01        50       CASH
   3                2008-10-03        50       CREDIT CARD

Show all sales with various payment methods:

NUMBER    DATE                TOTAL    METHOD
   2                2008-10-01        10       CREDIT CARD
   2                2008-10-05        15       BANK TRANSFER

I have been trying UNION, INTERACT, etc... queries with no success.

Thanks for your patience and please forgive my previous explanation.


"Eduardo [HPro]" <contato@hpro.com.br> escribió en el mensaje de
noticias:F606080C-9A2F-450C-A6B7-B7DCD5FCC3CA@news.elevatesoft.com...
> Santy
>
>> SALES_TABLE
>> NUMBER    DATE                TOTAL
>>    1               2008-10-01        50
>>    2               2008-10-03        25
>>    3               2008-10-03        50
>>
>> PAYMENT_TABLE
>> NUMBER    DATE                TOTAL    PAYMENT-METHOD
>>    2               2008-10-03        10            CREDIT CARD
>>    2               2008-10-05        15            BANK TRANSFER
>>
>> I would like to show ALL data in the following manner:
>>
>> NUMBER    DATE                TOTAL    PAYMENTS
>>    1                2008-10-01        50                0
>>    2                2008-10-03        25                25
>>    3                2008-10-03        50                0
>
> select number, sales_table.date, sales_table.total, sum(total)
> from payment_table
> inner join sales_table on (payment_table.number = sales_table.number)
> group by number
>
> Eduardo
>

Santy

Well, if I understand it correctly then

> SALES_TABLE
> NUMBER    DATE                TOTAL
>    1               2008-10-01        50
>    2               2008-10-03        25
>    3               2008-10-03        50
>
> PAYMENT_TABLE
> NUMBER    DATE                TOTAL    PAYMENT-METHOD
>    2               2008-10-03        10            CREDIT CARD
>    2               2008-10-05        15            BANK TRANSFER
>
> I would like to show ALL data in the following manner:
>
> NUMBER    DATE                TOTAL    PAYMENTS
>    1                2008-10-01        50                0
>    2                2008-10-03        25                25
>    3                2008-10-03        50                0

select number, sales_table.date, sales_table.total, sum(total), 'Other'
from payment_table
inner join sales_table on (payment_table.number = sales_table.number)
union all
select number, date, total, total, payment_method
from sales_table where payment_method in ('Cash','Credit card','Other
method')
group by number

But I will suggest you to add at least one record to payment table. It will
make easier this kind of query.

Eduardo

>
> SALES_TABLE
>  NUMBER    DATE                TOTAL    PAYMENT-METHOD
>     1               2008-10-01        50        CASH
>     2               2008-10-03        25        {SEVERAL METHODS}
>     3               2008-10-03        50        CREDIT CARD
>
> PAYMENT_TABLE
>  NUMBER    DATE                TOTAL    PAYMENT-METHOD
>     2               2008-10-03        10            CREDIT CARD
>     2               2008-10-05        15            BANK TRANSFER
>
>
> I need to show all sales WITH UNIQUE payments methods, and all sales WITH
> SEVERAL payments methods.
> Don't matter if it is shown in one simple query or separate ones.
>
> For example (if possible with one simple query)
>
>  NUMBER    DATE                TOTAL    PAYMENTS    METHOD
>     1                2008-10-01        50                0
CASH
>     2                2008-10-03        25                10
CREDIT CARD
>     2                2008-10-05        25                15
BANK  TRANSFER
>     3                2008-10-03        50                0
CREDIT CARD

If this is the result you want (ie separate lines for each payment method)
then try

SELECT number,date,total,0 as payments,payment_method FROM sales_table
WHERE number not in (SELECT number FROM payment_table)
UNION ALL
SELECT number,date,sales_table.total,payment_table.total as
payments,payment_table.payment_method FROM payment_table
JOIN sales_table ON payment_table.number=sales_table.number

John