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...).
Subject
Written By
Posted
Assistance required for query with subquery
December 29, 2010 07:41AM
December 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.