Home » SQL & PL/SQL » SQL & PL/SQL » Left join problem
Left join problem [message #429226] Mon, 02 November 2009 10:54 Go to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
Hello.
I have two tables, and want to compute sumary based on left join relationship between these tables. I need to get next result:
ID NAME PAYED TAXES
-- ------ ----- -------
2 Steve 3400 0
1 Eric 5300 2300

instead of:

ID NAME PAYED TAXES
-------- ---------- --------- -----------
2 Steve 0 0
1 Eric 5300 2300
Script is in attachment.
Thx in advance.
  • Attachment: script.sql
    (Size: 0.76KB, Downloaded 122 times)
Re: Left join problem [message #429229 is a reply to message #429226] Mon, 02 November 2009 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many of us can't or don't want to download files.
Post your test case inside the text.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

[Edit:] And post your current query.

Regards
Michel

[Updated on: Mon, 02 November 2009 11:11]

Report message to a moderator

Re: Left join problem [message #429230 is a reply to message #429226] Mon, 02 November 2009 11:18 Go to previous messageGo to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
Accounting table
EMP_ID YEAR TAXES DEPARTMENT_ID
1 2009 2000 10
1 2009 300 10

Employer table:
ID NAME SALARY MONTH
1 Eric 1500 6
1 Eric 2000 7
1 Eric 1800 8
2 Steve 2200 7
2 Steve 1200 8

Result:
ID NAME PAYED TAXES
2 Steve
1 Eric 5300 2300

instead of:
ID NAME PAYED TAXES
2 Steve 3400 0
1 Eric 5300 2300
Query:
select distinct id,name,
(select sum(salary) from employer where id=emp_id) payed,
(select sum(taxes) from accounting where id=emp_id) taxes
from employer, accounting
where id=emp_id(+)
Oracle version:10.2.0.1
Re: Left join problem [message #429234 is a reply to message #429230] Mon, 02 November 2009 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 02 November 2009 18:10
Many of us can't or don't want to download files.
Post your test case inside the text.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags and align the columns in result.

Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

[Edit:] And post your current query.

Regards
Michel

Post a working Test case: create table and insert statements along with the result you want with these data.

Re: Left join problem [message #429236 is a reply to message #429234] Mon, 02 November 2009 11:56 Go to previous messageGo to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
create table employer
(id number,
name VARCHAR2(10),
salary number,
month number)

insert into employer values (1,'Eric',1500,06);
insert into employer values (1,'Eric',2000,07);
insert into employer values (1,'Eric',1800,08);
insert into employer values (2,'Steve',2200,07);
insert into employer values (2,'Steve',1200,08);

create table accounting
(emp_id number,
year number,
taxes number,
department_id number)

insert into accounting values (1,2009,2000,10);
insert into accounting values (1,2009,300,10);
Re: Left join problem [message #429237 is a reply to message #429236] Mon, 02 November 2009 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 02 November 2009 18:53
Michel Cadot wrote on Mon, 02 November 2009 18:10
...
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags and align the columns in result.

Use the "Preview Message" button to verify.

...

What is your problem in doing this?

Joins

Regards
Michel

[Updated on: Mon, 02 November 2009 12:19]

Report message to a moderator

Re: Left join problem [message #429240 is a reply to message #429237] Mon, 02 November 2009 12:31 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Fortunately I didn't need exact formatting to understand what you meant. Does this work for you?

SQL> select id,name, sum(salary),sum(taxes)
  2  from employer, accounting
  3  where id=emp_id(+)
  4  group by id,name;

        ID NAME       SUM(SALARY) SUM(TAXES)
---------- ---------- ----------- ----------
         2 Steve             3400
         1 Eric             10600       6900

SQL>
Re: Left join problem [message #429241 is a reply to message #429240] Mon, 02 November 2009 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Fortunately I didn't need exact formatting to understand what you meant

I do neither. Just a point of respect.

Regards
Michel
Re: Left join problem [message #429248 is a reply to message #429240] Mon, 02 November 2009 14:42 Go to previous messageGo to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
andrew again wrote on Mon, 02 November 2009 19:31
Fortunately I didn't need exact formatting to understand what you meant. Does this work for you?

SQL> select id,name, sum(salary),sum(taxes)
  2  from employer, accounting
  3  where id=emp_id(+)
  4  group by id,name;

        ID NAME       SUM(SALARY) SUM(TAXES)
---------- ---------- ----------- ----------
         2 Steve             3400
         1 Eric             10600       6900

SQL>


Andrew, thx a lot (some guys obviously need more than script file for help), but I actually need to get:
        ID NAME       SUM(SALARY) SUM(TAXES)
---------- ---------- ----------- ----------
         2 Steve             3400          0
         1 Eric              5300       2300
Re: Left join problem [message #429250 is a reply to message #429248] Mon, 02 November 2009 15:26 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
NVL will do it. (when null then return 0).
SQL> select id,name, nvl(sum(salary), 0) sum_sal,nvl(sum(taxes), 0) sum_tx
  2  from employer, accounting
  3  where id=emp_id(+)
  4  group by id,name;

        ID NAME          SUM_SAL     SUM_TX
---------- ---------- ---------- ----------
         2 Steve            3400          0
         1 Eric            10600       6900

SQL>
Re: Left join problem [message #429251 is a reply to message #429250] Mon, 02 November 2009 15:37 Go to previous messageGo to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
Andrew, please pay attention on both rows. You got correct result for only first row.
Re: Left join problem [message #429290 is a reply to message #429251] Tue, 03 November 2009 01:18 Go to previous messageGo to next message
manu_jariwala
Messages: 20
Registered: August 2005
Location: Surat
Junior Member

Hi,

Please try this....

select id,name,
(select sum(nvl(salary,0)) from employer where id=a.id) payed,
(select nvl(sum(taxes),0) from accounting where emp_id=a.id) taxes
from employer a
group by id,name;

R'gards,
Manish Jariwala
Re: Left join problem [message #429342 is a reply to message #429226] Tue, 03 November 2009 05:01 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Two ways:
SELECT id,name, sum(salary), nvl(t.tax,0) FROM employer e,
(SELECT emp_id, SUM(taxes) tax
 FROM accounting
 GROUP BY emp_id) t
WHERE e.id = t.emp_id(+)
GROUP BY id,name, t.tax;

SELECT id,name, sum(salary), 
nvl((SELECT SUM(taxes) FROM accounting WHERE emp_id = ID),0) tax
FROM employer e
GROUP BY id,NAME;


You can't directly join the tables because you need to simulate a 1-1 join where you don't actually have one.
Previous Topic: Queue with built-in datatype
Next Topic: why the absence of "partition by" gives diffreent results?
Goto Forum:
  


Current Time: Thu Sep 29 20:58:32 CDT 2016

Total time taken to generate the page: 0.26577 seconds