CODEWITHSUNDEEP
mysqlsqljoincount
user1383191
user1383191

Reputation: 33

MySQL - Select row if appear more than x times in other table

Lets say I have two tables, Members and Orders with a 1:N relation:

Members         | Orders
UserID  Name    | OrderID   UserID  Name
111     Peter   | 777       111     Peter
222     Bart    | 888       333     Joe
333     Joe     | 999       111     Peter
444     Andrew  | 101       444     Andrew
                | 102       111     Peter
                | 103       333     Joe

I am trying to get the Members ID from the Members table, in case that Member has more than 1 order in the Orders table.

So the result should be...

Members
UserID  Name
111     Peter
333     Joe

...because Peter and Joe both have at least 2 orders in the Orders table.

I have tried to get a result with:

SELECT
    s.UserID,
FROM Members s
    INNER JOIN Orders o
        ON s.UserID = o.UserID
WHERE
    s.UserID IN
    (
        SELECT UserID
        FROM Orders
        GROUP BY UserID
        HAVING COUNT(*) > 5
    )

But that gives me Peter back 3 times, and Joe 2 times; so I get duplicates instead of each UserID once. How can I get the result without duplicates?

Upvotes: 3

Views: 6764

Answers (5)

Ruud van de Ven
Ruud van de Ven

Reputation: 216

Here the answer, try to avoid DISTINCT

SELECT 
 UserID
 ,counts.count
FROM
 Members mem

JOIN (SELECT UserID,COUNT(*) as 'count' FROM Orders WHERE COUNT(*) > 1 GROUP BY UserID) counts
ON mem.UserID = counts.UserID

Upvotes: 0

dns_nx
dns_nx

Reputation: 3943

Is it this what your looking for?

SELECT
    s.UserID,
    s.Name
FROM 
    Members s
INNER JOIN 
    Orders o ON s.UserID = o.UserID
GROUP BY
    s.UserID
HAVING
    COUNT(*) > 5

Upvotes: 3

zarruq
zarruq

Reputation: 2465

You just need a subquery having count(OrderID) > 1 as below.

SELECT *
FROM members
WHERE UserID IN (
        SELECT UserId
        FROM orders
        GROUP BY userid
        HAVING count(OrderID) > 1
        );

Result:

UserID  Name
--------------
111     Peter 
333     Joe

DEMO

Upvotes: 1

kchason
kchason

Reputation: 2885

I'm assuming (and hoping) that you're not storing the user's name twice, since that leads to data quality issues when the user changes their name.

Assuming the tables are structured like below:

CREATE TABLE
  Members
(
    UserID INT,
    Name VARCHAR(15)
);

INSERT INTO
  Members
VALUES
(111, 'Peter'),
(222, 'Bart'),
(333, 'Joe'),
(444, 'Andrew');

CREATE TABLE
  Orders
(
   OrderID INT,
   UserID INT
);

INSERT INTO
  Orders
VALUES
(777, 111),
(888, 333),
(999, 111),
(101, 444),
(102, 111),
(103, 333);

You can use a GROUP BY and HAVING clause which would give you the UserID of all users with more than 1 (or whichever number you choose) orders. Then, you join that to the Members table to get the name.

SELECT
  Orders.UserID,
  Members.Name
FROM
  Orders
INNER JOIN
  Members
  ON Orders.UserID = Members.UserID
GROUP BY
  UserID,
  Members.Name
HAVING
  COUNT(OrderID) > 1;

SQLFiddle: http://sqlfiddle.com/#!9/1dadc4/2

However, if you already have the names stored (and that's not changing), then you could skip the JOIN like below:

SELECT
  UserID,
  Name
FROM
  Orders
GROUP BY
  UserID,
  Name
HAVING
  COUNT(OrderID) > 1

Upvotes: 7

Tim Rogers
Tim Rogers

Reputation: 496

To remove duplicates, use DISTINCT.

SELECT
DISTINCT
    s.UserID
FROM Members s
    INNER JOIN Orders o
        ON s.UserID = o.UserID
WHERE
    s.UserID IN
    (
        SELECT UserID
        FROM Orders
        GROUP BY UserID
        HAVING COUNT(*) > 5
    )

Upvotes: 0

Related Questions