mysql建立中间表语句,基于中间表中的列选择的MySql语句

在数据库中,我有一个用户名表和管辖区表.我还有一个中间表,用于将用户分配到一个或多个司法管辖区.

员工表

> userID(主键)

>名字

>姓氏

记录：

+--------+-----------+----------+

| userID | firstName | lastName |

+--------+-----------+----------+

| 6 | John | Doe |

| 11 | lisa | lopez |

+--------+-----------+----------+

管辖区表

> jurId(主键)

>地区

记录：

+-------+--------------+

| jurID | jurisdiction |

+-------+--------------+

| 1 | California |

| 2 | Texas |

| 3 | Washington |

| 4 | South Dakota |

| 5 | Alaska |

| 6 | Ohio |

+-------+--------------+

user_jurisdiction

> userID(指向员工userID的外键)

> jurID(指向辖区jurID的外键)

记录：

+--------+-------+

| userID | jurID |

+--------+-------+

| 6 | 2 |

| 6 | 3 |

| 11 | 2 |

+--------+-------+

我一直在努力几个小时,想出一条SQL语句,该语句将选择/列出“德克萨斯州”的所有工人.我一直在使用此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";

但是我没有成功.哪条sql语句将获得辖区所涉雇员的列表.jurisdiction =“ Texas”;

解决方法:

您现在正在做的是从表(员工和辖区)中生产Caterian产品.连接的正确语法是显式定义两个表之间的连接类型.

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'

当前查询的输出

╔════════╦═══════════╦══════════╦═══════╦══════════════╗

║ USERID ║ FIRSTNAME ║ LASTNAME ║ JURID ║ JURISDICTION ║

╠════════╬═══════════╬══════════╬═══════╬══════════════╣

║ 6 ║ John ║ Doe ║ 2 ║ Texas ║

║ 11 ║ lisa ║ lopez ║ 2 ║ Texas ║

╚════════╩═══════════╩══════════╩═══════╩══════════════╝

要进一步获得有关联接的知识,请访问以下链接：

标签：join,select,sql,mysql

来源： https://codeday.me/bug/20191031/1972892.html