在mysql中使用select from where命令有问题

Question

大家好，对于所有这些数据库的东西，大家都是新手，他们尝试着让一个测试数据库为一个大学项目工作。

我有一个名为customers和caravans的表，还有一个名为customers_caravans的表，它将两者联系在一起。

然后我有一个名为previous works的终结表。这详细介绍了前面针对customer_caravanID的工作。

然而，似乎不能计算出where语句来仅提取特定客户的前一项工作的数据。

它似乎遍历了整个客户列表？

对于某些人来说，这将是一个简单的修复，无论是我的select命令是错误的，或者可能只是我的数据库的整个设计。

不管怎样，这是数据库..。

describe customers;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| customerID     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name           | varchar(256) | NO   |     | NULL    |                |
| street_address | varchar(256) | YES  |     | NULL    |                |
| postcode       | varchar(256) | YES  |     | NULL    |                |
| telephone      | varchar(256) | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+


describe caravans;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| caravanID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| make        | varchar(256) | NO   |     | NULL    |                |
| model       | varchar(256) | NO   |     | NULL    |                |
| vin_number  | varchar(256) | YES  |     | NULL    |                |
| axle_number | tinyint(4)   | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

describe customers_caravans;
+----------------------+---------+------+-----+---------+----------------+
| Field                | Type    | Null | Key | Default | Extra          |
+----------------------+---------+------+-----+---------+----------------+
| customers_caravansID | int(11) | NO   | PRI | NULL    | auto_increment |
| customerID           | int(11) | NO   | MUL | NULL    |                |
| caravanID            | int(11) | NO   | MUL | NULL    |                |
+----------------------+---------+------+-----+---------+----------------+

describe previous_works;
+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| customers_caravansID | int(11)       | NO   | MUL | NULL    |       |
| work                 | varchar(256)  | YES  |     | NULL    |       |
| partID               | int(11)       | YES  | MUL | NULL    |       |
| date                 | date          | YES  |     | NULL    |       |
| cost                 | decimal(13,2) | YES  |     | NULL    |       |
+----------------------+---------------+------+-----+---------+-------+

其中包含以下数据...

INSERT INTO customers (name,street_address,postcode,telephone) VALUES 
('Megan Rose Hoobar', '41-43 Barrows Street', 'IP14 6HJ', '01449 77777'),
 ('Tim 
Smith', '17 Wynton Rise', 'JF77 2AB', '01564 673742');

INSERT INTO caravans (make,model,vin_number,axle_number) VALUES 
('Elddis','Buccaneer Commodore','1HGBH41JXMN1091866','2'), 
('Lunar','Venus 590/6','1HGBH41JXMN1091111','1');

INSERT INTO customers_caravans (customerID,caravanID) VALUES ('1','1'), ('2','2');

INSERT INTO previous_works (customers_caravansID,work,partID,date,cost) 
VALUES 
('1','Service','3','2015-04-15','150'),
('1','Service','3','2016-04-15','150'), 
('1','Service','5','2017-04-15','180'),
('2','Solar Panel Fitting','1','2018-02-18','380');

即这个选择，我只想获得数据的客户谁的customers_caravansID匹配1，即在这种情况下，只有我得到的服务，两个客户重复...

select x.name
     , y.make
     , y.model
     , p.work 
  from caravans y
     , customers x
     , customers_caravans xy
     ,previous_works p
 where p.customers_caravansID = "1";
+----------------------+--------+---------------------+---------+
| name                 | make   | model               | work    |
+----------------------+--------+---------------------+---------+
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
+----------------------+--------+---------------------+---------+

Answer 1

这是您的查询，使用的是连接多个表的最新形式。注意它是如何关联每个表的。

SELECT
    b.name,
    a.make,
    a.model,
    d.work
FROM caravans a
JOIN customers_caravans c
    ON a.caravamID = c.caravanID
JOIN customers b
    ON a.customerID = b.customerID
JOIN previous_works d
    ON c.customers_caravansID = d.customers_caravansID
WHERE d.customers_caravansID = "1";