检查一个表的列中的所有值是否都存在于另一个表中

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?

我想知道检查一个表中的所有值(使用select语句创建)是否存在于另一个表(使用select命令创建)中的所有值都在一个select语句中的命令。例如,我有一个属性fid和faculty_name in教师表和fid,class_name,room_no在另一个。我如何检查在所有教室里教授的所有教师?

3 个解决方案

#1

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

#2

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

#3

-1

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.

假设你有两个表:教师和班级。 Fid(教师ID)应该是教师表上的主键,以及类表上的外键。

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

#1