MySQL查询和2个单独的表

So I have 2 tables in a MySQL database, one for "Users" and the other for "Orders", where each entry in Orders is an order placed by a User (Some users can have multiple orders, while some might not have any). I am working on reporting queries and one of the reports I want is a list of Users that have never placed any orders. I am still a bit of a beginner when it comes to MySQL queries, so I am not sure how to do this one. The query will be placed via PHP if that makes any difference.

所以我在MySQL数据库中有2个表，一个用于“用户”，另一个用于“订单”，其中订单中的每个条目都是用户下的订单（有些用户可以有多个订单，而有些可能没有订单）。我正在处理报告查询，我想要的一个报告是从未下过任何订单的用户列表。在MySQL查询方面，我仍然是一个初学者，所以我不知道如何做到这一点。如果这有任何区别，查询将通过PHP放置。

Here are my columns in each table (simplified):

以下是每个表中的列（简化）：

Users:

用户：

ID
Name

Orders:

命令：

OrderID
OrderName
CustomerID      (corresponds to User.ID that placed the order)

Any help here would be great. Thanks!

这里的任何帮助都会很棒。谢谢！

4 个解决方案

#1

Use a LEFT JOIN on Orders and check whether OrderId is null:

在订单上使用LEFT JOIN并检查OrderId是否为空：

SELECT U.*
FROM
 Users U
 LEFT JOIN Orders O ON(U.ID = O.CustomerID)
WHERE
 O.OrderId IS NULL

SQLFiddle

#2

SELECT ID, Name
FROM users 
WHERE ID NOT IN (SELECT DISTINCT customerID FROM Orders)

#3

So basically you'are looking for orders that are not made by that user

所以基本上你一直在寻找那些不是由那个用户做出的订单

Select * from Orders where CustomerID = 5 //5 is the id of the custumer

If you are doing a search by name

如果您按名称进行搜索

Select * from Orders LEFT JOIN Users on Users.ID Where Users.name ='Mark'

If no results, it mean that customer never ordered anything

如果没有结果，则意味着客户从未订购过任何东西

#4

Try

尝试

SELECT U.*
FROM   Orders a
           RIGHT JOIN Users b 
               ON b.ID = a.CustomerID
WHERE  a.OrderId IS NULL

#1