Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2010
    Posts
    7

    Question Unanswered: Assistance required for query with subquery

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    7
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2010
    Posts
    7
    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?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2010
    Posts
    7
    unfortunately is has the same effect as described above. I keep getting multiple AddressID combined with LowestOrderID. No idea why...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •