Home » SQL & PL/SQL » SQL & PL/SQL » JOIN (Oracle 9i)
JOIN [message #312451] Tue, 08 April 2008 22:45 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Friends,

I need output from 3 tables (This is sample only,, group by is may not match to my requirement).


people 

empid   Name
1001    Kala 
1002    Ram
1003    Suja

address

empid   Name
(No Data's)

assignment 

empid  City
1001   Chennai
1001   Chennai
1002   Bangalore
1003   Singapore
1003   Singapore   

When i am using this query

select * from people,address,assignment where
people.empid=address.empid (+)
and people.empid=assignment.em

I am getting output

empid  Name   empid_1 Name_1   empid_2  name_2
1001   Kala                    1001     Chennai
1001   Kala                    1001     Chennai
1002   Ram                     1002     Bangalore
1003   Suja                    1003     Singapore
1003   Suja                    1003     Singapore

I want to output look like this (Without Duplication from Assignment Table) using Joins

empid  Name   empid_1 Name_1   empid_2  name_2
1001   Kala                    1001     Chennai
1002   Ram                     1002     Bangalore
1003   Suja                    1003     Singapore


Please post your reply

Thanks
Manohar
Re: JOIN [message #312453 is a reply to message #312451] Tue, 08 April 2008 22:55 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
SELECT DISTINCT
Re: JOIN [message #312461 is a reply to message #312451] Tue, 08 April 2008 23:56 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Friend,

Actuall that is sample only. Actually my query is

select distinct * from people,address,assignment,per_assignments_f where
people.empid=address.empid (+)
and people.empid=assignment.empid
and people.empid=per_assignments_f.person_id

per_assignments_f table is (Oracle Apps HRMS Table)

I am getting duplicates now.

Thanks
Mano
Re: JOIN [message #312463 is a reply to message #312451] Wed, 09 April 2008 00:01 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Actuall that is sample only. Actually my query is
My actual response is
You're On Your Own (YOYO) for wasting my time.
Re: JOIN [message #312469 is a reply to message #312461] Wed, 09 April 2008 00:17 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't have duplicate rows if you used DISTINCT.

By the way what is the relation between your title "JOIN" and your requirement "I don't want duplicates"?

Regards
Michel

[Updated on: Wed, 09 April 2008 00:18]

Report message to a moderator

Previous Topic: automation script
Next Topic: Unstable Query Execution Time
Goto Forum:
  


Current Time: Mon Dec 05 21:24:23 CST 2016

Total time taken to generate the page: 0.09897 seconds