SQL查询只返回第一个客户的结果

My sql query only returns results for orders that belong to the first customer in the database. Where have I gone wrong?

我的sql查询只返回属于数据库中第一个客户的订单的结果。我哪里做错了?

SELECT orders.id, 
       orders.created_at, 
       orders.customer_id, 
       customers.firstname, 
       customers.surname, 
       customers.email, 
       customers.telephone, 
       customer_addresses.address_1, 
       customer_addresses.address_2, 
       customer_addresses.address_2, 
       customer_addresses.city,
       customer_addresses.county, 
       customer_addresses.post_code 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.id 
INNER JOIN customer_addresses ON customers.id = customer_addresses.id 
ORDER BY orders.id 
LIMIT 0, 10;

2 个解决方案

#1

if all the customers have address_id as you mentioned in your reply to @ceyquem's answer, I think you are joining customer_address table on wrong column. It should be

如果所有客户都像您在回复@ceyquem的回复中提到的那样拥有address_id，那么我认为您加入了customer_address表的错误列。它应该是

SELECT orders.id, 
       orders.created_at, 
       orders.customer_id, 
       customers.firstname, 
       customers.surname, 
       customers.email, 
       customers.telephone, 
       customer_addresses.address_1, 
       customer_addresses.address_2, 
       customer_addresses.address_2, 
       customer_addresses.city,
       customer_addresses.county, 
       customer_addresses.post_code 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.id 
INNER JOIN customer_addresses ON customers.address_id = customer_addresses.id 
ORDER BY orders.id 
LIMIT 0, 10;

Hope it works

希望它的工作原理

#2

If all your customers don't have a defined address, it might be the reason, try changing the JOIN operators as follows to test. Also, remove the limit condition at the end to ensure you can see all the result:

如果您的所有客户都没有定义的地址，这可能是原因所在，请尝试更改连接操作符，如下所示进行测试。同时，在最后去掉限制条件，以确保您可以看到所有的结果:

SELECT orders.id, 
       orders.created_at, 
       orders.customer_id, 
       customers.firstname, 
       customers.surname, 
       customers.email, 
       customers.telephone, 
       customer_addresses.address_1, 
       customer_addresses.address_2, 
       customer_addresses.address_2, 
       customer_addresses.city,
       customer_addresses.county, 
       customer_addresses.post_code 
FROM orders 
LEFT JOIN customers ON orders.customer_id = customers.id 
LEFT JOIN customer_addresses ON customers.id = customer_addresses.id 
ORDER BY orders.id;

#1