Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    May 2009
    Location
    Birmingham, England
    Posts
    58
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Multiple table join problem

    I'm having a problem whereby one of the joins I am making seems to be ignored but I have very little experience with joins and so I'm either doing it wrong or it isn't possible.

    Let me explain.

    I have the four following tables which relate to products and orders for the products. I have only included the relevant fields.

    Name: Products (Product Information)
    Fields: ID

    Name: Versions (Products can have one or more versions eg. red and blue)
    Fields: ID, Product_ID

    Name: Orders (Contains all info about orders eg. timestamp, customer info)
    Fields: ID, Timestamp

    Name: Ordered_Items (This contains the info about which product versions were bought on each order)
    Fields: Version_ID, Order_ID, Quantity

    What I need to do is find the total number of each version sold since a particular time.

    Here is what I have but it seems to ignore the timestamp given and just find all that have ever been sold.

    Code:
    SELECT SUM(oi.`Quantity`) AS `Quantity`
    FROM `Products` AS p 
    JOIN `Versions` AS v ON v.`Product_ID` = p.`ID` 
    LEFT JOIN `Ordered_Items` AS oi ON oi.`Version_ID` = v.`ID` 
    LEFT JOIN `Orders` AS o ON o.`Timestamp` > 13044862257 && o.`ID` = oi.`Order_ID` 
    WHERE p.`ID` = 212 
    GROUP BY m.`ID`
    The reason for the left joins is because in the actual query I also get other fields from the versions table but there may not have been any ordered.

    Any help would be greatly appreciated.

    Thanks.

    Mark

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    Is `TIMESTAMP` just an INT field in that table??? That's the only way the query makes sense, as written.

    If `TIMESTAMP` (bad name, as it's a keyword in MYSQL) happens to be a DATETIME or TIMESTAMP (yes, that's now the keyword as a data type) then you need to use a DATETIME expression instead of an integer to compare against. Or convert to unix time.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    Oh, never mind! You answered your own question: Because you used a LEFT JOIN, whether or not the ORDERS record is part of the result or now will *NOT* affect the ORDERED_ITEMS.QUANTITY !!!

    If you only want ordered items that match those orders, then you must INNER JOIN between ORDERED_ITEMS and ORDERS.

    Untested, but maybe this:
    Code:
    SELECT SUM(oi.`Quantity`) AS `Quantity`
    FROM `Products` AS p 
    INNER JOIN `Versions` AS v ON v.`Product_ID` = p.`ID` 
    LEFT JOIN 
        ( `Ordered_Items` AS oi 
          INNER JOIN `Orders` AS o ON o.`Timestamp` > 13044862257 && o.`ID` = oi.`Order_ID` 
        ) ON oi.`Version_ID` = v.`ID` 
    WHERE p.`ID` = 212
    Your GROUP BY was bogus, but I suspect you knew that.

    This still assumes the `TIMESTAMP` is an INT field. Why would you do that?

    If that doesn't work, we could re-order the tables and use a RIGHT JOIN no doubt.

  • Users who have thanked Old Pedant for this post:

    markspark100 (08-09-2011)

  • #4
    New Coder
    Join Date
    May 2009
    Location
    Birmingham, England
    Posts
    58
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Old Pedant once again you've come to my rescue...you seem to solve all my problems so thanks for that.

    I did try to do something like that ie putting brackets around that section but I didn't (obviously) do it correctly.

    Yes it is just an INT field and that's just because I didn't know any better. The timestamp stored is the unix timestamp created by php. Why should I not use an INT field? And how would you recommend I store it?

    Thanks.

    Ps. Are you sure the group by is bogus as it only fetches one result if I omit it even if there are multiple versions?
    Last edited by markspark100; 08-09-2011 at 09:53 AM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    Well you were doing "GROUP BY m.id" and you don't have any table aliased to "m" in there, so clearly that was wrong.

    If you want a quantity PER VERSION, then you need to have a version field in your SELECT and GROUP BY on that.

    Possibly:
    Code:
    SELECT v.versionName, SUM(oi.`Quantity`) AS `Quantity`
    FROM `Products` AS p 
    INNER JOIN `Versions` AS v ON v.`Product_ID` = p.`ID` 
    LEFT JOIN 
        ( `Ordered_Items` AS oi 
          INNER JOIN `Orders` AS o ON o.`Timestamp` > 13044862257 && o.`ID` = oi.`Order_ID` 
        ) ON oi.`Version_ID` = v.`ID` 
    WHERE p.`ID` = 212
    GROUP BY v.versionName
    The rule is simple: If you have one or more NON-aggregate fields in your SELECT, then GROUP BY each and every one of them. If you don't, the results are unpredictable. (And illegal in any DB I know of except MySQL, but that's another story.) Aggregate fields are those that use a function to collect values from multiple records, such as COUNT(), SUM(), AVG() and GROUP_CONCAT().

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    Regarding timestamps: I suppose the right answer depends on whether you want to manipulate and/or filter records based on dates in SQL or on PHP. If you never do any date manipulation in SQL and always do it all in PHP, then INT for a timestamp isn't bad.

    But if you ever want to do something such as "Find all records within the last week" or "Find all records between these two dates" then I *STRONGLY* advocate for using a DATETIME field.

    And then the SQL becomes not only trivial but also readable:
    Code:
    SELECT * FROM table WHERE datefield > DATE_SUB( NOW(), INTERVAL 1 WEEK )
    
    SELECT * FROM table WHERE datefield BETWEEN '2011-6-1' AND '2011-7-15'
    You *can* do this even if you have an INT field as a timestamp, since MySQL does include functions to convert back and forth:
    Code:
    SELECT * FROM table WHERE FROM_UNIXTIME(timestampfield) > DATE_SUB( NOW(), INTERVAL 1 WEEK )
    But that means that your queries won't be as efficient, especially if datefield is indexed. Because now MySQL can't use efficient indexing techniques, as it has no choice but to run the FROM_UNIXTIME function on EVERY record in the table.

  • #7
    New Coder
    Join Date
    May 2009
    Location
    Birmingham, England
    Posts
    58
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Ah yes sorry it should have been v.ID not m.ID dunno what happened there :s and yeah in my actual query I do get other info from the versions table.

    Regarding the timestamp I'll look into that. Thanks.

    Again thanks for all your help.


  •  

    Posting Permissions

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