Home » SQL & PL/SQL » SQL & PL/SQL » full outer join or union
full outer join or union [message #440243] Thu, 21 January 2010 15:30 Go to next message
kanaka
Messages: 2
Registered: January 2010
Junior Member
HI All,

I am working on one requirement.but I am not able to get the right direction to work on.Can you please help me.

I have 2 source tables

table1

emplid effdt name
xxxxx 01/01/2001 abc
xxxxx 01/02/2001 abcd


table2

emplid effdt marital_status
xxxxx 01/01/2001 U
xxxxx 01/04/2001 M
xxxxx 01/06/2001 D

the output would like this- target table

emplid effdt name marital_status
xxxxx 01/01/2001 abc U
xxxxx 01/02/2001 abcd U (most recent)
xxxxx 01/04/2001 abcd M
xxxxx 01/06/2001 abcd D


Can you please give me some ida how to acheive this

Thanks
Re: full outer join or union [message #440250 is a reply to message #440243] Thu, 21 January 2010 16:26 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Sure, if you provide the logic behind the requirements
Re: full outer join or union [message #440251 is a reply to message #440243] Thu, 21 January 2010 16:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

[Updated on: Thu, 21 January 2010 16:30]

Report message to a moderator

Re: full outer join or union [message #440265 is a reply to message #440243] Thu, 21 January 2010 22:33 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please read the Forum Guide lines.

Please provide a test case with valid syntax.
Insted of putting "xxxx" in your records use valid number(dummy)atleast.


sriram Smile
Re: full outer join or union [message #440285 is a reply to message #440243] Fri, 22 January 2010 00:47 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This might, or might not be one way to do that (depending on how much I've understood the question).

A test case:
SQL> create table table1
  2  (emplid varchar2(5),
  3   effdt  date,
  4   e_name varchar2(10)
  5  );

Table created.

SQL> create table table2
  2  (emplid         varchar2(5),
  3   effdt          date,
  4   marital_status varchar2(1)
  5   );

Table created.

SQL> insert all
  2    into table1 values ('xxxxx', to_date('01.01.2001', 'dd.mm.yyyy'), 'abc')
  3    into table1 values ('xxxxx', to_date('02.01.2001', 'dd.mm.yyyy'), 'abcd')
  4    into table2 values ('xxxxx', to_date('01.01.2001', 'dd.mm.yyyy'), 'U')
  5    into table2 values ('xxxxx', to_date('04.01.2001', 'dd.mm.yyyy'), 'M')
  6    into table2 values ('xxxxx', to_date('06.01.2001', 'dd.mm.yyyy'), 'D')
  7  select * from dual;

5 rows created.

Query:
SQL> select x.emplid,
  2         x.effdt,
  3         max(a.e_name) e_name,
  4         min(b.marital_status) marital_status
  5  from (select t1.emplid, t1.effdt
  6          from table1 t1
  7        union
  8        select t2.emplid, t2.effdt
  9          from table2 t2
 10       ) x,
 11       table1 a,
 12       table2 b
 13  where a.emplid = x.emplid
 14    and a.effdt <= x.effdt
 15    and b.emplid = x.emplid
 16    and b.effdt <= x.effdt
 17  group by x.emplid, x.effdt
 18  order by 1, 2
 19  ;

EMPLI EFFDT      E_NAME     M
----- ---------- ---------- -
xxxxx 01.01.2001 abc        U
xxxxx 02.01.2001 abcd       U
xxxxx 04.01.2001 abcd       M
xxxxx 06.01.2001 abcd       D

SQL>

So, asking about "full outer join OR union": here's a "join AND union" attempt.


Forgot to mention:ramoradba
Insted of putting "xxxx" in your records use valid number(dummy)atleast.

But, 'xxxx' IS a dummy value.

[Updated on: Fri, 22 January 2010 00:49]

Report message to a moderator

Re: full outer join or union [message #440564 is a reply to message #440265] Mon, 25 January 2010 14:10 Go to previous message
kanaka
Messages: 2
Registered: January 2010
Junior Member
HI Littlefoot,

Thanks for the solution.I tweeked the query little bit .It worked for me

Thanks
Previous Topic: sql query problem
Next Topic: Intersection of a large, unknown number of sets
Goto Forum:
  


Current Time: Sun Feb 09 07:55:22 CST 2025