CODEWITHSUNDEEP
sqloracle-database
user156073
user156073

Reputation: 2011

simple sql query

I have 3 tables as follows.

salesman(sid,sname)
location(lid,lname)
sales_loc(sid,lid)

Now i want to print the sid and sname of saleman who has visited all locations. I want a SQL query and I don't want a PL/SQL code.

Upvotes: 0

Views: 245

Answers (6)

P Sharma
P Sharma

Reputation: 2728

salesman(sid,sname) location(lid,lname) sales_loc(sid,lid)

select s.sid,s.sname from salesman s,location l, sales_loc sl where s.sid=sl.sid and l.lid=sl.lid

Upvotes: 0

Orson
Orson

Reputation: 15451

This should work. I've tried it out.

select a.sid, a.salesman, count(a.sid) as total from salesman a
inner join sales_loc b on b.sid = a.sid
inner join location c on c.lid = b.lid
group by a.sid, a.salesman
having count(a.sid) = (select count(*) from location)

It uses the total number of locations to compare.

Upvotes: 0

APC
APC

Reputation: 146249

One more for the pot!

Given these salesmen and their territories ...

SQL> select s.sname, l.lname
  2  from   salesman s
  3         , location l
  4         , sales_loc sl
  5  where  sl.sid = s.sid
  6  and sl.lid = l.lid
  7  order by s.sid, l.lid
  8  /

SNAME      LNAME
---------- ----------
FOX        TRAIN
FOX        BOAT
KNOX       BOAT
KNOX       HOUSE
SAM        TRAIN
SAM        BOAT
SAM        HOUSE

7 rows selected.

SQL>

... this query extracts the one who has visited all of them...

SQL> select s.sname
  2  from   salesman s
  3  where s.sid not in  (
  4      select sid from (
  5          select cjs.sid, cjl.lid
  6          from   salesman cjs
  7                 cross join location cjl
  8          minus
  9          select sl.sid, sl.lid
 10          from   sales_loc sl
 11      )
 12  )
 13  /

SNAME  
------ ----
SAM

SQL>

Upvotes: 0

Nick Dandoulakis
Nick Dandoulakis

Reputation: 43130

Another approach:

select sid, sname from salesman
where
  (select count(*) from location) =
  (select count(*) from sales_loc where sales_loc.sid = salesman.sid)

Edit: In case sales_loc(sid,lid) pair isn't a key, the below query is more appropriate,
as ammoQ suggested:

select sid, sname from salesman
where
  (select count(*) from location) =
  (select count(distinct lid) from sales_loc where sales_loc.sid = salesman.sid)

Upvotes: 4

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

select sid, sname from salesman
where not exists 
        (select 1 from location
          where not exists
                  (select 1 from sales_loc
                    where sid=saleman.sid 
                     and lid = location.lid));

Upvotes: 7

Will
Will

Reputation: 1591

Get all salesman recs where number of locations visited equals number of locations.

select sm.* from salesman as sm
where (select count(sl.*) from sales_loc as sl where sl.sid = sm.sid)
 = (select count(l.*) from location as l);

Upvotes: 0

Related Questions