SQL-检查任一表上是否存在数据 [英] SQL - Check if data exists on either table

问题描述

我的数据库中有两个表，分别用于我的两个用户(图书馆员和学生)的登录详细信息.我已将用户的详细信息分为两个单独的表tblUserLibrarian和tblUserStudent；一个给图书馆员，另一个给学生.每个表中的主键是用户名或ID(LibrarianID和StudentID).

I have two tables in my database regarding the login details for both of my users (Librarians and Students) I have separated the user's details into 2 separate tables tblUserLibrarian and tblUserStudent; one for the Librarians and one for the Students. The Primary key in each table is the username or ID (LibrarianID and StudentID).

表的轮廓如下:

tblUserStudent

StudentID   Password     FirstName    LastName

S1201235    pass3           Jane       Smith
S1201289    pass5           Pass       Word
S1202009    pass2           John       Smith

tblUserLibrarian

LibrarianID Password

L1094565    pass4
L1202836    password123
L1202908    qwerty
L1212345    pass3

我有一个问题，当任何一个用户尝试使用其用户名(即其ID)登录时.我想检查它们的用户名是否存在于任一表中，并从存在该用户名的表中返回所有字段.目前，我只能想到一种方法来执行2个查询来检查此内容，但是我认为可以在一个查询中完成.我查找了JOINS和UNIONS，但是我尝试过的所有方法似乎都无法正常工作.

I have a problem where when either user tries to login using their username (which is their ID). I want to check if their username exists on either table and return all the fields from the table on which the username exists. Currently I can only think of one way which is to do 2 queries to check this however I feel that this can be done in one query. I've looked up JOINS and UNIONS but everything I've tried doesn't seem to work.

我最近的查询是:

SELECT TOP 1 * FROM tblUserStudent,tblUserLibrarian
WHERE StudentID = "S1202836" OR LibrarianID = "S1202836"

但是这将返回两个表中的行，如果用户输入的用户名存在，我只想从一个表中返回用户的详细信息.

But this returns rows from both tables, I just want to return the details of the user from one table if the username they entered exists.

我正在使用MS Access 2010进行查询.

I am using MS Access 2010 to do my queries.

推荐答案

您的表没有相同的结构.您可以对两个表都执行UNION ALL来进行查询，但是只返回一些关于图书馆员的信息:

Your tables don't have the same structure. You could do a UNION ALL to do the query on both tables, but only return some information for Librarians:

SELECT TOP 1 * 
FROM(
  SELECT studentId AS userID, password, firstName, LastName
  FROM tblUserStudent 
  WHERE StudentID = 'S1201235'
  UNION ALL
  SELECT LibrarianID,password, NULL, NULL
  FROM tblUserLibrarian 
  WHERE LibrarianID = 'S1201235'
) a

sqlfiddle演示 (sql服务器，但仅作为示例)

sqlfiddle demo (sql server, but serves as an example)

我在ID的列中添加了一个别名，以向您显示userID而不是StudentID，因为UNION从第一个SELECT中获取列名.

I added an alias to the id's column to show you userID instead of studentID, since UNION takes the column names from the first SELECT.

我也离开了TOP 1，但是如果您的ID是唯一的，那么您应该只收到一个ID，这样就无关紧要了

I also left the TOP 1, but if your ID's are unique, you should receive only one, making it irrelevant

这篇关于SQL-检查任一表上是否存在数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！