mardi 9 juin 2015

Joining tables where two columns match

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