MySQL Forums :: Newbie :: Assistance required for query with subquery


Advanced Search

Assistance required for query with subquery
Posted by: w vink ()
Date: December 29, 2010 07:41AM

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



In the the table Sync_Orders there is another field called kitchen_id.
The kitchen_id is unique. Same OrderID 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...).

Options: ReplyQuote


Subject Written By Posted
Assistance required for query with subquery w vink 12/29/2010 07:41AM
Re: Assistance required for query with subquery Rick James 12/31/2010 11:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.