CODEWITHSUNDEEP
mysqlsql
gator
gator

Reputation: 3523

Select all rows with values that appear twice by the same customer?

I have a table:

CREATE TABLE Orders (
  ID INT,
  Customer INT,
  PRIMARY KEY(ID)
);

CREATE TABLE Items (
  ID INT,
  Barcode INT,
  PRIMARY KEY(ID, Barcode)
);

INSERT INTO Orders VALUES
  (1, 1), (2, 1), (3, 2), (4, 3), (5, 3);

INSERT INTO Items VALUES
  (1, 1), (1, 2), (1, 3), (1, 7),
  (2, 1), (2, 3), (3, 2), (3, 8),
  (4, 2), (4, 3), (4, 8), (5, 4);

I'm trying to find all customers who have ordered the same item twice and specify the item, but not from the same order. I just need a list of Orders.Customer and Items.Barcode showing this.

Here's a query that helps illustrate:

SELECT i.ID, i.Barcode, o.Customer
FROM Items i, Orders o
WHERE i.ID = o.ID

Which produces the below:

+----+---------+----------+
| ID | Barcode | Customer |
+----+---------+----------+
|  1 |       1 |        1 | # A
|  1 |       2 |        1 | 
|  1 |       3 |        1 | # B
|  1 |       7 |        1 |
|  2 |       1 |        1 | # A
|  2 |       3 |        1 | # B
|  3 |       2 |        2 |
|  3 |       8 |        2 |
|  4 |       2 |        3 |
|  4 |       3 |        3 |
|  4 |       8 |        3 |
|  5 |       4 |        3 |
+----+---------+----------+

Note where I tagged A, Barcode 1 appears in both ID 1 and ID 2. Both those orders have the same customer, same barcode, but different order IDs. B is another example.

How can I pull out these rows, so I have something like the below:

+---------+----------+
| Barcode | Customer |
+---------+----------+
|       1 |        1 |
|       3 |        1 |
+---------+----------+

More declaratively, I want to know what customers have ordered the same item twice, and list the items and customers. In other words, "Customer 1 has ordered Items 1 and 3 twice".

Upvotes: 1

Views: 1588

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

I'm trying to find all customers who have ordered the same item twice and specify the item, but not from the same order.

This is pretty simple with a HAVING clause:

SELECT  o.Customer, i.Barcode
FROM Orders o JOIN
     Items i
     ON i.ID = o.ID
GROUP BY  o.Customer, i.Barcode
HAVING MIN(o.id) <> MAX(o.id);

Note the use of proper, explicit, standard, readable JOIN syntax. Never use commas in the FROM clause.

Upvotes: 1

Rajeev Pande
Rajeev Pande

Reputation: 476

You may try this -

With cte1 as (SELECT i.ID as orderId, i.Barcode, o.Customer
FROM Items i, Orders o
WHERE i.ID = o.ID),
cte2 as (SELECT i.ID as orderId, i.Barcode, o.Customer
FROM Items i, Orders o
WHERE i.ID = o.ID)
Select distinct cte1.Barcode, cte1.Customer
from cte1, cte2
where 
cte1.Barcode = cte2.Barcode 
and cte1.Customer = cte2.Customer
and cte1.orderId <> cte2.orderId;

More elegant way - Please refer Amit Verma's answer

Upvotes: 0

Amit Verma
Amit Verma

Reputation: 2490

If you need the expected result then you should group by with having clause like below.

SELECT i.Barcode, o.Customer
FROM Items i, Orders o
WHERE i.ID = o.ID
GROUP BY i.Barcode, o.Customer HAVING COUNT(*) >1

I am assuming that you need all records which are repeating more than once.

Upvotes: 0

Related Questions