MySql语句，根据中间表中的一列进行选择

In a database, I have a username table and I have jurisdiction table. I also have a intermediate table to assign a user to one or more jurisdictions.

在数据库中，我有用户名表和权限表。我还有一个中间表，可以将用户分配到一个或多个权限。

employee table

员工表

• userID (primary Key)
• 用户标识(主键)
• firstName
• firstName
• lastName
• 姓

records:

记录:

+--------+-----------+----------+
| userID | firstName | lastName |
+--------+-----------+----------+
|      6 | John      | Doe      |
|     11 | lisa      | lopez    | 
+--------+-----------+----------+

jurisdictions table

权限表

• jurId (Primary Key)
• jurId(主键)
• region
• 地区

records:

记录:

+-------+--------------+
| jurID | jurisdiction |
+-------+--------------+
|     1 | California   |
|     2 | Texas        |
|     3 | Washington   |
|     4 | South Dakota |
|     5 | Alaska       |
|     6 | Ohio         |
+-------+--------------+

user_jurisdiction

user_jurisdiction

• userID (Foriegn Key pointing to employees userID)
• userID (Foriegn键指向员工userID)
• jurID (Foriegn Key pointing to jurisdictions jurID)
• jurID(指向司法管辖区的Foriegn Key)

records:

记录:

    +--------+-------+
    | userID | jurID |
    +--------+-------+
    |      6 |     2 |
    |      6 |     3 |
    |     11 |     2 |
    +--------+-------+

I've been trying for hours to come up with a sql statement that would select/list all the workers from "Texas". I've been using many altercations of this sql statement but no success:

我花了好几个小时的时间来设计一个sql语句，它将选择/列出来自“Texas”的所有工作人员。我对这个sql语句进行了多次修改，但没有成功:

SELECT  jurisdictions.jurisdiction,
        employees.firstName
FROM    jurisdictions,
        employees
        INNER JOIN user_jurisdictions
            ON  user_jurisdictions.jurID = jurisdictions.jurID AND 
                user_jurisdictions.userID = employees.userID
WHERE   jurisdictions.jurisdiction = "Texas";

But I have had no success. What sql statement would get a list of employees that are involved from jurisdictions.jurisdiction = "Texas";

但我没有成功。sql语句将获得涉及权限的雇员列表。管辖=“德州”;

2 个解决方案

#1

What you are doing right now is you are producing catersian product from tables: employees and jurisdictions. The proper syntax of joins is to explicit define the type of join between two tables.

你现在所做的就是从表格中生产毛虫产品:雇员和辖区。连接的正确语法是显式地定义两个表之间的连接类型。

SELECT  a.*, c.*
FROM    employees a
        INNER JOIN user_jurisdiction b
            ON a.userID = b.userID
        INNER JOIN jurisdictions c
            ON b.jurID = c.jurID
WHERE   c.jurisdiction = 'Texas'

• SQLFiddle Demo
• SQLFiddle演示

OUTPUT of the current query

当前查询的输出。

╔════════╦═══════════╦══════════╦═══════╦══════════════╗
║ USERID ║ FIRSTNAME ║ LASTNAME ║ JURID ║ JURISDICTION ║
╠════════╬═══════════╬══════════╬═══════╬══════════════╣
║      6 ║ John      ║ Doe      ║     2 ║ Texas        ║
║     11 ║ lisa      ║ lopez    ║     2 ║ Texas        ║
╚════════╩═══════════╩══════════╩═══════╩══════════════╝

To further gain more knowledge about joins, kindly visit the link below:

为了进一步了解加入的更多信息，请访问下面的链接:

• Visual Representation of SQL Joins
• SQL连接的可视化表示

#2

SELECT 
 e.*
FROM 
 jurisdictions j, user_jurisdiction uj, employees e
WHERE
 uj.jurID = j.jurID AND 
 uj.userID = e.userID AND
 j.jurisdiction = 'Texas';