CODEWITHSUNDEEP
sqloracle-databasedatabase
user1783427
user1783427

Reputation: 41

check if all values in a column of one table exists in another table

I wanted to know the command to check if all the values in one table(created using select statement) is present in the other table (created using select command) all in one select statement.for eg ,i have a attribute fid and faculty_name in faculty table and fid ,class_name, room_no in another. how do i check all faculty who teaches in all the room present?

Upvotes: 3

Views: 15078

Answers (3)

demo.b
demo.b

Reputation: 3449

You can try something like this,

select a.faculty_name, b.class_name, b.room_no 
  from faculty a, Table2 b 
 where a.fid = b.fid

Upvotes: 0

Luke Liu
Luke Liu

Reputation: 298

Let’s say you have two tables: faculty and class. Fid (faculty id) should be the primary key on faculty table, and foreign key on class table.

Here only can be two cases you are looking for: all faculties have class or only some faculties.

To find who has class:

SELECT
  fid,
  faculty_name
FROM
  faculty f
  INNER JOIN
  class c
  ON
      f.fid = c.fid

To find who don’t have class:

SELECT
  fid,
  faculty_name
FROM
   faculty f
   LEFT OUTER JOIN
   class c
   ON
      f.fid = c.fid
WHERE
  c.fid is null

Upvotes: -1

Nicholas Carey
Nicholas Carey

Reputation: 74355

Poorly asked question, but

--
-- all faculty without a class
--
select *
from faculty f
where not exists ( select *
                   from class c
                   where c.fid = f.fid
                 )
--
-- all classes wihout faculty
--
select *
from class c
where not exists ( select *
                   from faculty f
                   where f.fid = c.fid
                 )
--
-- all-in-one. Each returned row represents
-- either a faculty or class without a match
-- in the other
--
select *
from      faculty f
full join class   c on c.fid = f.fid
where c.fid is null
   or f.fid is null

Upvotes: 8

Related Questions