I have a small database of various computer parts and prices. There are 2 tables, Parts and Prices.
Parts:
partID desc mfgr timeStamp
---------- ---------- ---------- ----------
1 RAM Crucial 1
2 MOBO MSI 1
3 I7 970 Intel 1
1 RAM Crucial 2
Prices:
productID qty price timeStamp
---------- ---------- ---------- ----------
1 1 50.0 1
1 2 100.0 1
1 3 130.0 1
2 1 140.0 1
3 1 499.99 1
3 1 449.99 2
1 4 150.0 2
2 1 150.0 2
1 1 40.0 2
1 4 200.0 3
I need to grab everything from Parts that has the most recent timestamp, and GROUP_CONCAT(price) for all of the prices that match both the partID and the timestamp. So the output should look something like this:
partID desc mfgr timeStamp GROUP_CONCAT(price)
---------- ---------- ---------- ---------- -------------------
1 RAM Crucial 2 150.0,40
2 MOBO MSI 1 140.0
3 I7 970 Intel 1 499.99
I'm really close, but not quite getting the right results. I have tried
SELECT * FROM Parts INNER JOIN
(SELECT partID, MAX(Parts.timeStamp) as maxTS, GROUP_CONCAT(price) FROM
Parts, Prices WHERE partID = Prices.productID GROUP BY partID) grouped
ON Parts.partID = grouped.partID AND Parts.timeStamp = grouped.maxTS;
But this grabs everything from the pricing table where the part ID matches, whether or not the timestamp also matches.
partID desc mfgr timeStamp partID maxTS GROUP_CONCAT(price)
---------- ---------- ---------- ---------- ---------- ---------- -------------------
2 MOBO MSI 1 2 1 140.0,150.0
3 I7 970 Intel 1 3 1 449.99,499.99
1 RAM Crucial 2 1 2 40.0,50.0,100.0,130
So I changed my command to
SELECT * FROM Parts INNER JOIN
(SELECT partID, MAX(Parts.timeStamp) AS maxTS, GROUP_CONCAT(price)
FROM Parts, Prices
WHERE partID = Prices.productID AND (SELECT MAX(parts.timestamp) FROM Parts) = Prices.timeStamp) grouped
ON Parts.partID = grouped.partID AND Parts.timeStamp = grouped.maxTS;
But this only matches rows from pricing that have the largest timestamp that occurs in parts. (Which is 2)
What am I doing wrong here?
Aucun commentaire:
Enregistrer un commentaire