sql Select Orders with multiple addresses

Question

I'm trying to select orders, which are send at least two times with the same addressId to a customer.

This is my table structure:

Customer Table:

+------------+-----------+
| customerId | addressId |
+------------+-----------+
|          1 |         1 |
|          2 |         2 |
|          3 |         3 |
|          4 |         4 |
+------------+-----------+

Relation for Addresses to Orders

+---------+-----------+
| orderId | addressId |
+---------+-----------+
|       1 |         1 |
|       2 |         2 |
|       3 |         2 |
|       4 |         3 |
|       5 |         4 |
|       6 |         4 |
+---------+-----------+

Order Table

+----+------------+-------+
| id | orderEntry | total |
+----+------------+-------+
|  1 | timestamp  |  4711 |
|  2 | timestamp  |  0815 |
|  3 | timestamp  |  1337 |
+----+------------+-------+

Now I want a output like this:

+------------+---------+-----------+
| customerId | orderId | addressId |
+------------+---------+-----------+
|          2 |       2 |         2 |
|          2 |       3 |         2 |
|          4 |       5 |         4 |
|          4 |       6 |         4 |
+------------+---------+-----------+

I've tried to get the right result with these Query, but I think I can't count the addresses this way.

SELECT C.`customerId`, AO.`orderId`, AO`addressId`
FROM customer AS C
  JOIN address_order AS AO ON AO.addressId = C.addressId
  JOIN order AS O ON O.id = AO.orderId
GROUP BY AO.`orderId`
HAVING (COUNT(AO.`addressId`) > 1);

With these Query I only get a result like this:

+------------+---------+-----------+
| customerId | orderId | addressId |
+------------+---------+-----------+
|          2 |       2 |         2 |
|          4 |       5 |         4 |
+------------+---------+-----------+

The general GROUP BY rule says: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function!" (Older MySQL doesn't care about this, and returns an arbitrary result. Newer MySQL are more strict, and wont execute invalid queries.) — jarlh

Abhik Chakraborty Abhik Chakraborty · Accepted Answer · 2016-08-18T08:13:24

I don't see the usage of order table here. However you can use the order table into the consideration if you want to make sure that the order table data and address_order should have data. You can write the query as

select
c.customerId,
ao.orderId,
ao.addressId
from customer c
join address_order ao on ao.addressId = c.addressId
join (
 select addressId, count(*) as tot from address_order 
 group by addressId having tot = 2
)x on x.addressId = ao.addressId

If you want to make sure all the orderId from customer_order are in the order table then you can add another join at the end as

join `order` o on o.id = ao.orderId

sql Select Orders with multiple addresses

2 Answers