使用UNION在多个表中选择 [英] Selecting across multiple tables with UNION

问题描述

我将数据库从一个用户表重做为多个用户表(按角色划分):tblStudents，tblTeachers，tblAdmin

I reworked my database from one user table to multiple user tables (divided per role): tblStudents, tblTeachers, tblAdmin

登录时，我不想运行三个查询来检查用户是否存在于数据库中.因此，我所做的就是将以下查询与union

When logging in, I didn't want to run three queries to check if the user exists somewhere in my DB. So what I did was put together the following query with union

select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t

这将选择所有表中相同的字段，并为我很好地输出结果.

This selects the fields that are the same across all tables and outputs the results nicely for me.

因此，我决定尝试一下，由于某种原因，我的登录表单无法使用.对于我的登录表单，我添加了一个where子句来检查电子邮件地址.我在数据库应用程序中运行了查询，令人惊讶的是，当我进行例如where email = "[e-mail]"(此电子邮件存在于数据库tblAdmin中)时，它还从我的学生表中选择了一条记录.

So, I decided to try this and for some reason, my login form wouldn't work. For my login form, I added a where clause which checks for the email address. I ran the query in my database app and surprisingly, when I do for example where email = "[e-mail]" (this email exists in my database tblAdmin), it also selects a record from my students table.

带有where子句:

select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
where email = "[e-mail]"

记录都为have id = 1，但我不明白为什么我在过滤管理电子邮件地址时会选择学生记录.为什么是这样?有人可以解释这个问题并为我提供更好的解决方案吗?我基本上只有一个登录表单，需要在多个表中进行选择以检查用户是否存在于我的数据库中.

The records both have id = 1 but I don't understand why it would select the student record when I'm filtering on the admin email address. Why is this? Can someone explain and provide me with a better solution to this problem? I basically have one login form and need to select across multiple tables to check if the user exists in my db.

推荐答案

感谢更新查询；现在我们可以看到WHERE条件仅应用于 last UNIONed查询.您需要或者将该WHERE子句添加到每个查询中，或将其包装为子选择并对其应用WHERE子句.

Thanks for updating the query; now we can see that the WHERE condition is only applied to the last UNIONed query. You need to either add that WHERE clause to each query, or wrap it as a subselect and apply the WHERE clause to that.

select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
where email = "[e-mail]"
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
where email = "[e-mail]"
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
where email = "[e-mail]"

或

SELECT * FROM (
select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
) foo where email = "[e-mail]"

这篇关于使用UNION在多个表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！