OraFAQ Forum: SQL & PL/SQL » find missing record

Home » SQL & PL/SQL » SQL & PL/SQL » find missing record (ORACLE 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

find missing record [message #646086]

Sat, 19 December 2015 04:46

nmd_group
Messages: 21
Registered: February 2012
Location: Dhaka,Bangladesh

Junior Member

emp_id ------- year
A-1 ------- 2007
A-1 ------- 2008
A-1 ------- 2010
A-1 ------- 2011

A-2 ------- 2007
A-2 ------- 2008
A-2 ------- 2009
A-2 ------- 2011

----------------------------------------------------
I need missd information, as bellow.
A-1 ------- 2009
A-2 ------- 2010

Report message to a moderator

Re: find missing record [message #646088 is a reply to message #646086]

Sat, 19 December 2015 04:57

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Outer join.

Report message to a moderator

Re: find missing record [message #646089 is a reply to message #646088]

Sat, 19 December 2015 05:01

nmd_group
Messages: 21
Registered: February 2012
Location: Dhaka,Bangladesh

Junior Member

Kindly show the example.

Report message to a moderator

Re: find missing record [message #646090 is a reply to message #646089]

Sat, 19 December 2015 05:04

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Search for "outer join with calendar" and you will find many examples.

Report message to a moderator

Re: find missing record [message #646095 is a reply to message #646090]

Sat, 19 December 2015 08:04

nmd_group
Messages: 21
Registered: February 2012
Location: Dhaka,Bangladesh

Junior Member

Here is only one table.

Report message to a moderator

Re: find missing record [message #646096 is a reply to message #646095]

Sat, 19 December 2015 08:12

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes and you can generate yourself a calendar.

Report message to a moderator

Re: find missing record [message #646097 is a reply to message #646096]

Sat, 19 December 2015 09:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Calendar post
http://www.orafaq.com/forum/mv/msg/95011/463394/102589/#msg_463394

Report message to a moderator

Re: find missing record [message #646098 is a reply to message #646096]

Sat, 19 December 2015 09:40

nmd_group
Messages: 21
Registered: February 2012
Location: Dhaka,Bangladesh

Junior Member

Here is the calendar.
-----------------------
WITH
months AS (
SELECT 2006+level year,
level month
FROM dual
CONNECT BY LEVEL <= 5
)
SELECT year FROM months

Report message to a moderator

Re: find missing record [message #646099 is a reply to message #646098]

Sat, 19 December 2015 09:44

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Good now outer join it to your table.

Report message to a moderator

Re: find missing record [message #646100 is a reply to message #646098]

Sat, 19 December 2015 09:48

nmd_group
Messages: 21
Registered: February 2012
Location: Dhaka,Bangladesh

Junior Member

You know, i need more help here. i am not so expert right now.

Report message to a moderator

Re: find missing record [message #646102 is a reply to message #646100]

Sat, 19 December 2015 10:01

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Read Join.

Report message to a moderator

Previous Topic:	Same SQL work differently in different SQL Plus
Next Topic:	Kind help with a pl sql for the below link I had posted

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 25 13:04:31 CDT 2026