Joining 2 tables without repetitions

Hello,

I have two tables and I need to join them using SQL MS query without repetitions from oracle Database,
Table 1 : Has Customer Payments
Customer,Month,Year, Payment Amount

Table 2 : Has Obligations
Vendor,Month,Year,Payment Amount

I need to see the result
Month,Year,Amount (Amount Either Minus or positive)

Thank you
m_jundiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Without some sample data and expected results, this would be a total guess.

I see no way to join the tables and I am going to assume that vendors are negative and customers are positive.  Based on output, I would think you aren't trying to join them anyway.

select month, year, sum(amount)
from (select month, year, payment_amount amount from customers union all
select month, year, payment_amount * - 1 amount from vendor)
group by month, year;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuaMin ChenProblem resolverCommented:
TRY
select a.month,a.year,sum(a.payment_amount)-sum(b.payment_amount)
from customer_payment a,obligations b
where a.month=b.month
and a.year=b.year
group by a.month,a.year;

Open in new window

johnsoneSenior Oracle DBACommented:
Joining on month and year between the 2 tables will result in missing data if one table has records for a month and one doesn't.
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

m_jundiAuthor Commented:
HuaMinChen, tried this before to join using dates but missed some data,

johnsone , please check this query, it gave me "FROM keyword not found where expected"


select
DAY,
MONTH,  
YEAR
SUM(AMOUNT)
from
(select
TO_NUMBER(TO_CHAR(CUSTOMERS.due_date,'dd')) DAY,
TO_NUMBER(TO_CHAR(CUSTOMERS.due_date,'mm')) MONTH,  
TO_NUMBER(TO_CHAR(CUSTOMERS.due_date,'yyyy')) YEAR,
CUSTOMERS.AMOUNT AMOUNT FROM CUSTOMERS
WHERE
CUSTOMERS.COMP = 1 AND
CUSTOMERS.CODE = 1 AND
CUSTOMERS.STATUS = 2
UNION ALL
SELECT
TO_NUMBER(TO_CHAR(VENDORS.DUE_DATE,'DD')) DAY,
TO_NUMBER(TO_CHAR(VENDORS.DUE_DATE,'MM')) MONTH,
TO_NUMBER(TO_CHAR(VENDORS.DUE_DATE,'YYYY')) YEAR
PAYMENT_AMOUNT AMOUNT
FROM VENDORS WHERE VENDORS.STATUS='U')
group by
DAY,
MONTH,  
YEAR;
m_jundiAuthor Commented:
Even Adding Comma after Year , same thing
johnsoneSenior Oracle DBACommented:
Missing comma on line 4 after the YEAR column.
johnsoneSenior Oracle DBACommented:
Also missing comma on line 21 after YEAR column.
m_jundiAuthor Commented:
it Worked , but it will give net amount for for a certain date, can I have 2 columns for Customers and vendors amounts ?
johnsoneSenior Oracle DBACommented:
I believe this should do it
SELECT Nvl(a.day, b.day)     DAY, 
       Nvl(a.month, b.month) MONTH, 
       Nvl(a.year, b.year)   YEAR, 
       SUM(Nvl(a.amount, 0)) cust_total, 
       SUM(Nvl(b.amount, 0)) vend_total 
FROM   (SELECT To_number(To_char(customers.due_date, 'dd'))   DAY, 
               To_number(To_char(customers.due_date, 'mm'))   MONTH, 
               To_number(To_char(customers.due_date, 'yyyy')) YEAR, 
               customers.amount                               AMOUNT 
        FROM   customers 
        WHERE  customers.comp = 1 
               AND customers.code = 1 
               AND customers.status = 2) a 
       full outer join (SELECT To_number(To_char(vendors.due_date, 'DD'))   DAY, 
                               To_number(To_char(vendors.due_date, 'MM')) 
                               MONTH, 
                               To_number(To_char(vendors.due_date, 'YYYY')) YEAR 
                               , 
                               payment_amount 
                               AMOUNT 
                        FROM   vendors 
                        WHERE  vendors.status = 'U') b 
                    ON a.day = b.day 
                       AND a.month = b.month 
                       AND a.year = b.year 
GROUP  BY Nvl(a.day, b.day), 
          Nvl(a.month, b.month), 
          Nvl(a.year, b.year); 

Open in new window

Putting all of the information in the original request with sample data and expected results is very helpful.
m_jundiAuthor Commented:
Will you check it please,
"SQL command not properly ended"

SELECT
Nvl(a.day, b.day) DAY,
Nvl(a.month, b.month) MONTH,
Nvl(a.year, b.year) YEAR,
Nvl(a.due_date , b.Due_date) DUE_DATE,
SUM(Nvl(a.amount, 0)) CUST_TOTAL,
SUM(Nvl(b.amount, 0)) VEND_TOTAL
FROM  
(SELECT To_number(To_char(CUSTOMERS.due_date, 'dd')) DAY,
To_number(To_char(CUSTOMERS.due_date, 'mm')) MONTH,
To_number(To_char(CUSTOMERS.due_date, 'yyyy')) YEAR,
CUSTOMERS.Due_date DUE_DATE,
CUSTOMERS.amount AMOUNT
FROM CUSTOMERS
WHERE
CUSTOMERS.CO_COMPANY_CODE = 1 AND
CUSTOMERS.PNCHQ_CODE = 1 AND
CUSTOMERS.PNCHQ_STATUS = 2) a
full outer join
(SELECT
To_number(To_char(VENDORS.due_date, 'DD')) DAY,
To_number(To_char(VENDORS.due_date, 'MM')) MONTH,
To_number(To_char(VENDORS.due_date, 'YYYY')) YEAR,
VENDORS.due_date DUE_DATE,
PAYMENT_AMOUNT AMOUNT  
FROM VENDORS
WHERE VENDORS.status = 'U') b
ON
a.day = b.day AND
a.month = b.month AND
a.year = b.year AND
a.due_date = b.Due_date
GROUP  BY
Nvl(a.day, b.day),
Nvl(a.month, b.month),
Nvl(a.year, b.year),
Nvl(a.due_date , b.Due_date);
johnsoneSenior Oracle DBACommented:
You keep changing the columns.  If you are going to break the date out into three columns, why do you need the date too?

I don't get a syntax error with what you posted or with what I posted.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.