Assistance required for query with subquery

wovin · #1 (**permalink**) 12-29-10, 07:09

Hello, I have three tables and want to have a resultset where each unique AddressID is accompanied with lowest OrderID from table Sync_Orders. I've managed to create a GROUP BY AddressID and ORDER BY OrderID ASCbut the problem is that I have then (for example) 4 rows with the same AddressID and OrderID from low to high. If I want the unique/distinct AddressID, the OrderID is then random and cannot be trusted, MySQL does not support the LIMIT and GROUP BY I believe.

The only option, I guess, would be to work with subqueries. My knowledge of MySQL is limited and I cannot figure it out.

Below is a summary of the three tables. Multiple PhoneID's in the Sync_Orders table can be linked to one AddressID. I want to have unique AddressID with the lowest OrderID. I think the tables give a clear idea of what I want to achieve. Thanks for the help!

Table: Sync_Orders
OrderID | PhoneID |Orderdate

50 | 6 | 09/12/2010
49 | 5 | 08/12/2010
48 | 4 | 07/12/2010
47 | 3 | 06/12/2010
46 | 2 | 05/12/2010
45 | 1 | 04/12/2010
44 | 7 | 03/12/2010
43 | 9 | 02/12/2010
42 | 8 | 01/12/2010
41 | 7 | 30/11/2010

Table: Sync_Phone
PhoneID | AdresID

1 | 1
2 | 1
3 | 2
4 | 2
5 | 3
6 | 3
7 | 4
8 | 4
9 | 4

Table: Sync_Address
AddressID

1
2
3
4

Requested Result
Unique AddressID with lowest OrderID (== first Orderdate) from Sync_Orders

AddressID LowestOrderID Orderdate

1 | 45 | 04/12/2010
2 | 47 | 06/12/2010
3 | 49 | 08/12/2010
4 | 41 | 30/11/2010

r937 · #2 (**permalink**) 12-29-10, 07:19

Code:

SELECT Sync_Address.AddressID
     , Sync_Orders.OrderID
     , Sync_Orders.OrderDate
  FROM Sync_Address
INNER
  JOIN Sync_Phone
    ON Sync_Phone.AdresID = Sync_Address.AddressID
INNER
  JOIN ( SELECT PhoneID
              , MIN(OrderDate) AS first_order
           FROM Sync_Orders
         GROUP
             BY PhoneID ) AS m
    ON m.PhoneID = Sync_Phone.PhoneID
INNER
  JOIN Sync_Orders
    ON Sync_Orders.PhoneID = m.PhoneID
   AND Sync_Orders.OrderDate = m.first_order

wovin · #3 (**permalink**) 12-29-10, 08:06

Thanks for the quick reply!

In the the table Sync_Orders there is another field called kitchen_id.
The kitchen_id is unique. Same order numbers can be applied to several kitchen_id's. If i want to get the result set for one specific kitchen I need to add a WHERE clause somewhere. let's say WHERE kitchen_id = 6, where to put this clause? or does this mean that another subquery is needed ( I Hope not...).

I though it would be easy to add some WHERE clauses but I've tried and it gives me an invalid resultset.

r937 · #4 (**permalink**) 12-29-10, 08:19

Code:

SELECT Sync_Address.AddressID
     , Sync_Orders.OrderID
     , Sync_Orders.OrderDate
  FROM Sync_Address
INNER
  JOIN Sync_Phone
    ON Sync_Phone.AdresID = Sync_Address.AddressID
INNER
  JOIN ( SELECT PhoneID
              , MIN(OrderDate) AS first_order
           FROM Sync_Orders
          WHERE kitchen_id = 6
         GROUP
             BY PhoneID ) AS m
    ON m.PhoneID = Sync_Phone.PhoneID
INNER
  JOIN Sync_Orders
    ON Sync_Orders.PhoneID = m.PhoneID
   AND Sync_Orders.OrderDate = m.first_order
   AND Sync_Orders.kitchen_id = 6

wovin · #5 (**permalink**) 12-29-10, 08:53

thannks again, just checked the query and found out that my result set does contain multiple AddressID's.

In my AddressID table if have 3000 records so this query should output also 3000 records with lowestOrderID and orderdate. If I run the query i get a different total and multiple AddressID's. At the other hand, at all AdressID's the correct lowestOrderID is attached. Resultset looks now like this:

AddresID | LowestOrderID
1 | 41
1 | 41
1 | 41
2 | 44
2 | 44
3 | 45
4 | 46
4 | 46
4 | 46
4 | 46
5 | 46

Probably a distinct on AddressID should solve the problem, that's what I thought, but that did not work? Do you know why this is happening?

r937 · #6 (**permalink**) 12-29-10, 09:17

try it like this instead --

Code:

SELECT Sync_Address.AddressID
     , Sync_Orders.OrderID
     , Sync_Orders.OrderDate
  FROM Sync_Address
INNER
  JOIN Sync_Phone
    ON Sync_Phone.AdresID = Sync_Address.AddressID
INNER
  JOIN ( SELECT PhoneID
              , MIN(OrderID) AS first_id
           FROM Sync_Orders
          WHERE kitchen_id = 6
         GROUP
             BY PhoneID ) AS m
    ON m.PhoneID = Sync_Phone.PhoneID
INNER
  JOIN Sync_Orders
    ON Sync_Orders.PhoneID = m.PhoneID
   AND Sync_Orders.OrderID = m.first_id
   AND Sync_Orders.kitchen_id = 6

wovin · #7 (**permalink**) 12-29-10, 09:29

unfortunately is has the same effect as described above. I keep getting multiple AddressID combined with LowestOrderID. No idea why...

Internet Services

Web Development

Digital Marketing

Consumer Tech