...

View Full Version : Multiple table join problem



markspark100
08-08-2011, 05:03 PM
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.


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 :)

Old Pedant
08-08-2011, 09:18 PM
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.

Old Pedant
08-08-2011, 09:23 PM
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:


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.

markspark100
08-09-2011, 10:41 AM
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?

Old Pedant
08-09-2011, 09:20 PM
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:


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().

Old Pedant
08-09-2011, 09:28 PM
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:


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:


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.

markspark100
08-09-2011, 10:02 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum