sql select with join and where子句-从两个表中选择一条记录

rjzwgtxy  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(291)

我得到了这样的东西:

MySqlCommand sqlcmd = new MySqlCommand(@"SELECT Pupil.*, Instructor.* 
    FROM Pupil
    INNER JOIN Instructor ON Pupil.email = Instructor.email
    WHERE Pupil.email = '" + userEmail.Text + "'
      and Pupil.password = '" + userPassword.Text + "'", sqlconn);

我需要得到一个隐藏在第一个或第二个表中的记录。我检查并在第二个表中插入了相同的数据。这个select显示的是公共记录,但我只需要第一个或第二个表中的唯一记录。

kiz8lqtg

kiz8lqtg1#

在您的评论之后,我修改了查询。以下是示例数据的两个示例:
学生
adam@.com
bob@.com
讲师
bob@.com (请注意,您的数据结构实际上允许这样做)
chris@.com
查询

/* Adam is a pupil but not an instructor */
select Pupil.*
from Pupil
left join Instructor on Pupil.email = Instructor.email
where Instructor.email is null
  and Pupil.email = 'Adam@abc.com'
union 
select Instructor.*
from Instructor
left join Pupil on Pupil.email = Instructor.email
where Pupil.email is null
  and Instructor.email = 'Adam@abc.com';

/* Chris is an instructor but not a pupil */
select Pupil.*
from Pupil
left join Instructor on Pupil.email = Instructor.email
where Instructor.email is null
  and Pupil.email = 'Chris@abc.com'
union 
select Instructor.*
from Instructor
left join Pupil on Pupil.email = Instructor.email
where Pupil.email is null
  and Instructor.email = 'Chris@abc.com';

您可以在以下网址找到一个工作示例:http://sqlfiddle.com/#!9/c490c7/28号

相关问题