CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Random guy
Random guy

Reputation: 923

How to get the data of one to many relationship in oracle using sql?

I have a table and there are numerous data.There is relation between memid and other columns is that memid is one to one with other columns. But,I want to find the case where same memid may have different firstname,lastname etc. My expected output is as follows:

enter image description here

Here the memid 1003,10008 is associated with different memfirstname and memlastname.So, I tried like this:

select memid from ot.emp group by memid having count(distinct memfirstname)>1;
select memid from ot.emp group by memid having count(distinct memlastname)>1;
select * from ot.emp where memid in ('1003','10008');

I got the output above by using the query above.But I needed to use the two different query. Cant i use (distinct memfirstname,memlastname)? Is,there any better query to get result of one to many mapping?

Upvotes: 0

Views: 34

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use exists :

select e.*
from ot.emp e
where exists (select 1 
              from ot.emp e1 
              where e1.memid = e.memid and 
                    (e.memfirstname <> e1.memfirstname or 
                     e.memlastname <> e1.memlastname
                    )
             );

If you want memid which are identical by both (memfirstname & memlastname) then use AND instead of OR in subquery.

Upvotes: 1

Related Questions