CODEWITHSUNDEEP
sqloracle11goracle10g
Ayushi midha
Ayushi midha

Reputation: 45

what should be the output of this query

Display the salesmen id, salesmen name and location of those salesmen who are co-located.

I have used self join to solve it

select s.sid, s.sname, s.location 
from salesman s 
inner join salesman ss on s.location = ss.location 
where s.location=ss.location

Salesman  Table

SID SNAME   LOCATION
1   Peter   London
2   Michael Paris
3   John    Mumbai
4   Harry   Chicago
5   Kevin   London
6   Alex    Chicago

Expected output

Expected Result
SID SNAME   LOCATION
5   Kevin   London
6   Alex    Chicago
1   Peter   London
4   Harry   Chicago

Upvotes: 0

Views: 6308

Answers (4)

Anukruti
Anukruti

Reputation: 87

Probably something like this:

select s.sid, s.sname, s.location 
from salesman s 
inner join salesman ss 
on s.sid <> ss.sid
and s.location = ss.location

Upvotes: 1

Subodh Sharda
Subodh Sharda

Reputation: 36

I have assumed that you are not particular about sequence which you have shown in expected output and used join which is much quicker .

select s.sid, s.sname, s.location 
from salesman s 
inner join (
  select location from salesman group by location having count(*) >1
) ss on s.location = ss.location

Upvotes: 2

forpas
forpas

Reputation: 164069

With EXISTS:

select s.*
from salesman s
where exists (
  select 1
  from salesman
  where sid <> s.sid and location = s.location
)

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

You can use a subquery to find the repeated locations, and then get all salesman on those locations in the main query, as in:

select *
from salesman
where location in (
  select location
  from salesman s
  group by location
  having count(*) > 1 
)

Upvotes: 0

Related Questions