Geek asks and answers

How to limit SQL LEFT JOIN query result set by account

I want to use the LEFT JOIN query to get some results from two tables with a one-to-many relationship, but limit the result set based on the count of children. I have two tables structured as:

customers
id    name    ...
1     "bob"   ...
2     "jill"  ...

orders
id    customer_id    ...
100   1              ...
101   2              ...
102   1              ...

(The rest of the data in the tables does not matter for this query.)

What I would like to do is get all the customer IDs and their order IDs, sorted by customer , but limited to the customers who placed several orders. . In this example, the results will look like this:

cust_id    order_id
1          100
1          102

LEFT JOIN, , , , , , .

SELECT 
    `customers`.`id` AS `cust_id`,
    `orders`.`id` AS `order_id`
FROM
    `customers`
LEFT JOIN `orders` ON
    `customers`.`id` = `orders`.`customer_id`
ORDER BY
    `cust_id`

.

+3
5

- , , . IN EXISTS, JOIN

SELECT 
    `customers`.`id` AS `cust_id`,
    `orders`.`id` AS `order_id`
FROM
    `customers`
LEFT JOIN `orders` ON
    `customers`.`id` = `orders`.`customer_id`
INNER JOIN 
      (SELECT `customer_id `    
       FROM `orders`
       GROUP BY `customer_id`    
       HAVING COUNT(id) > 1) as `morethanone`
On
    `customer`.`id`  = `morethanone`.`custmor_id`
ORDER BY
    `cust_id`
+7

-, , , , , , .

SELECT customers.id AS `cust_id`, orders.id AS `order_id`
FROM customers
     INNER JOIN orders ON `customers`.`id` = `orders`.`customer_id`
     INNER JOIN orders count_orders ON customers.id = count_orders.customer_id
GROUP BY count_orders.id, orders.id
HAVING count(count_orders.id) >= 2
ORDER BY `cust_id`
+1

-, , , . , .

SELECT `customers`.`id` AS `cust_id` , count( orders.id ) AS count_orders
FROM `customers`
JOIN `orders` ON `customers`.`id` = `orders`.`customer_id`
GROUP BY customers.id
HAVING count( orders.id ) >1
ORDER BY `cust_id`
+1

, , LEFT JOIN , ?

, COUNT.

SQL Server, 2005+, CTE count, , , :

WITH customerWithMoreThanOneOrder AS
(
    SELECT 
        `customers`.`id` AS `cust_id`
    FROM
        `customers`
    INNER JOIN `orders` ON
        `customers`.`id` = `orders`.`customer_id`
    GROUP BY 
        `customers`.`id`
    HAVING COUNT(0) > 1
)

SELECT 
    `customers`.`id` AS `cust_id`,
    `orders`.`id` AS `order_id`
FROM
    `customers`
INNER JOIN `orders` ON
    `customers`.`id` = `orders`.`customer_id`
WHERE `customers`.`id` IN (SELECT cust_id FROM customerWithMoreThanOneOrder)
ORDER BY
    `cust_id`

, , , count IN, :

SELECT 
    `customers`.`id` AS `cust_id`,
    `orders`.`id` AS `order_id`
FROM
    `customers`
INNER JOIN `orders` ON
    `customers`.`id` = `orders`.`customer_id`
WHERE `customers`.`id` IN (SELECT 
                               `customers`.`id` AS `cust_id`
                           FROM
                               `customers`
                           INNER JOIN `orders` ON
                               `customers`.`id` = `orders`.`customer_id`
                           GROUP BY 
                               `customers`.`id`
                           HAVING COUNT(0) > 1)
ORDER BY
    `cust_id`

, - .

0

IN.

SELECT 
    `customers`.`id` AS `cust_id`,
    `orders`.`id` AS `order_id`
FROM
    `customers`
LEFT JOIN `orders` ON
    `customers`.`id` = `orders`.`customer_id`
WHERE `customer_id` IN (SELECT `customer_id` FROM `orders` GROUP BY `customer_id` HAVING COUNT(*) > 1)
ORDER BY
    `cust_id`
0

More articles:


All Articles