mysql - 在两个表上使用内连接，外键引用主表上的主 ID

Question

假设我有下表:

品牌表

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ID        | varchar(20)  | NO   | PRI | NULL    |       |
| CarBrand  | varchar(100) | NO   |     | NULL    |       |
| FuelType  | varchar(100) | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

模型表

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| ID         | int(2)       | NO   |     | NULL    |       |
| Model      | varchar(100) | NO   |     | NULL    |       |
| BrandID    | varchar(100) | NO   | MUL | NULL    |       |
| TopSpeed   | varchar(100) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

这里有一些条目

+----+----------------+-------------------------+
| ID | CarBrand       | FuelType                |
+----+----------------+-------------------------+
| 0  | Ford           | Gasoline                |
| 1  | Chevy          | Gasoline                |
| 2  | Peterbilt      | Diesel                  |
| 3  | GMC            | Gasoline                |
| 4  | OshKosh        | Natural Gas             |
+----+----------------+-------------------------+

+----+---------------+---------+------------+
| ID | Model         | BrandID | TopSpeed   |
+----+---------------+---------+------------+
|  1 | F150          | 0       | 130MPH     |
|  2 | 1500          | 1       | 110MPH     |
|  3 | 389           | 2       | 70MPH      |
|  4 | 1500          | 3       | 110MPH     |
|  5 | Mustang       | 0       | 170MPH     |
|  6 | Cement Truck  | 4       | 65MPH      |
+----+---------------+---------+------------+

型号表中的 BrandID 列是品牌表主键 ID 列的外键。如果您无权访问任一列的值怎么办？我将如何编写一个 SQL 查询来使用 INNER JOIN 列出属于 Ford 的所有模型

Answer 1

没有访问 ID，这意味着您只有我猜的型号名称。所以知道“ford”你就可以得到像这样的两个ID...

select * from modelsTable Mod inner join brandsTable Bra
on Mod.id= ( select id from Bra where CarBrand='FORD')