How can I get the PKeys and Prices for each row in a different SQL table?

Question

I'm having trouble figuring out how I can best create the desired end result of this SQL query without nesting SELECT statements which just seems awful. So awful. I feel like it may have something to do with a PIVOT, but I'm trying to find the most efficient way to do this.

I have dumbed down these tables as an example. I have 2 tables: PRODUCTS and PRODUCT_PRICING. The PRODUCT_PRICING table will have multiple instances of each product for each week that the price has changed.

PRODUCTS
PKey   |   ProductName
------------------------
10         Hammer
24         Screw Driver
31         Nail
48         Wrench

PRODUCT_PRICING
PKey   |   ProductPKey   |   ProductPrice   |   WeekNumber
----------------------------------------------------------
1          10                10.00              20
2          10                9.50               21
3          24                6.00               21
4          31                0.01               15
5          31                0.02               21
6          48                7.50               21

What I'm trying to do is pass in a week number (21) and get all of the results in a structure like this:

WeekNumber | HammerPKey | HammerPrice | ScrewDriverPKey | ScrewDriverPrice | NailPKey | NailPrice | WrenchPKey | WrenchPrice
21           10           9.50          24                6.00               31         0.02        48           7.50

http://stackoverflow.com/questions/13985470/mysql-how-to-display-row-value-as-column-name-using-concat-and-group-concat — zookastos, Sep 30 '16 at 16:01
This is helpful, but I fear that doing the MAX calculations every time will add a lot to my execution time. However, playing around with it, I think I was able to create something with INNER JOINS that will work for me. Thanks for your assistance, Nalin. — Rob, Sep 30 '16 at 16:17
What if your rows in products increase? What is the complexity of your inner joins. I am interested in the solution please. — zookastos, Sep 30 '16 at 16:19
Writing it up now :) The product rows won't increase. It's for a fantasy sports concept I'm toying around with. I had attempted to make it more simple with the example, but maybe that misled the idea a bit. — Rob, Sep 30 '16 at 16:22

score 0 · Answer 1 · answered Sep 30 '16 at 16:35

Thanks to Nalin's link in the comments above, I ended up coming up with a solution. There may still be a better solution, but this seems to be working for now. If anybody has a more efficient idea, please let me know.

First I made a separate table PRODUCT_SELECTION that has columns HammerPKey, ScrewDriverPKey, NailPKey, WrenchPKey and WeekNumber. For my purposes, the number of products won't change. Then I ran this:

SELECT 
    HammerPKey, 
    Hammer.Price,
    ScrewDriverPKey, 
    ScrewDriver.Price,
    NailPKey, 
    Nail.Price,
    WrenchPKey, 
    Wrench.Price,
FROM PRODUCT_SELECTION 
INNER JOIN PRODUCT_PRICING AS Hammer
    ON Hammer.ProductPKey = PRODUCT_SELECTION.HammerPKey 
    AND Hammer.WeekNumber = PRODUCT_SELECTION.WeekNumber
INNER JOIN PRODUCT_PRICING AS ScrewDriver
    ON ScrewDriver.ProductPKey = PRODUCT_SELECTION.ScrewDriverPKey 
    AND ScrewDriver.WeekNumber = PRODUCT_SELECTION.WeekNumber
INNER JOIN PRODUCT_PRICING AS Nail
    ON Nail.ProductPKey = PRODUCT_SELECTION.NailPKey
    AND Nail.WeekNumber = PRODUCT_SELECTION.WeekNumber 
INNER JOIN PRODUCT_PRICING AS Wrench
    ON Wrench.ProductPKey = PRODUCT_SELECTION.WrenchPKey 
    AND Wrench.WeekNumber = PRODUCT_SELECTION.WeekNumber
WHERE PRODUCT_SELECTION.WeekNumber = 21

How can I get the PKeys and Prices for each row in a different SQL table?

1 Answers1