Complicated MySQL statement?

[hschillig]

Ok so this is very complicated...

I have three tables that are named Product_Sku, Product_Attr, Product_Attr_Vals

So say I have cereal (Special K). So I they can come in different sizes and colors. So I have a tab format... The tabs could have the sizes it comes in and then in those tabs, it could list the flavors that that size comes in.

So I have Product SKUs for different combinations.

• 12.7oz Chocolate Almond #30304040243
• 12.7oz Protein #30304040202
• 16oz Chocolate Strawberry #30304040204
• 16oz Protein #30304040209
• etc....

So I want tabs that have 12.7oz listed and then when you click on the 12.7oz, it will show these:

• Chocolate Almond #30304040243
• Protein #30304040202

Then under the 16oz tab, it will show this:

• Chocolate Strawberry #30304040204
• Protein #30304040209

Or whatever the data happens to be...

So to get my tabs, I have this query:

SELECT av.value, av.id
    FROM product_attr AS pa
    INNER JOIN product_attr_vals AS av ON av.attr_id = pa.id
    WHERE pa.deleted_at IS NULL
    AND pa.product_id = 4744
    AND pa.designator = 1
    GROUP BY av.value

That gives me:
Value: 12.7oz, ID: 1
Value: 16oz, ID: 5

Which I probably am grabbing the wrong ID's, not sure.
Here is my 2nd query so far:

SELECT av.value, av.id, av.sku_id
    FROM product_attr_vals AS av
    INNER JOIN product_attr AS pa ON pa.id = av.attr_id
    INNER JOIN product_sku AS s ON s.product_id = pa.product_id
    WHERE pa.product_id = 4744
    AND pa.designator = 0
    GROUP BY av.id
    ORDER BY sku_id

The designator column is 0 if the values are not a designator, but 1 if they are. So they will only have one designator for a product. A product has multiple SKUs. So I just want to list all the other attributes that are not a designator along with their value and SKU in a row.. like loop through. So I believe it will be two separate queries.

That query pulls back the following data:
ID: 6, Value: Original, SKU: #3103513504350
ID: 12, Value: Chocolate Almond, SKU: #35051302132
ID: 2, Value: Chocolate Almond, SKU: #56105450450454
ID: 14, Value: Protein, SKU: #6510651650650

The only ones that should pull back are the first two because they are the only SKUs that have a designator of 12.7oz as well.
Then on the 16oz tab, the last two should be the only ones showing.

Basically I need to only show the ones where that SKU also has the value of the designator.. But the value is in the same table as what I'm pulling from so it can't go in the WHERE clause.

I don't know if that helps at all? I know it's confusing..

[cereal]

Hi,
posting the structures of the tables would help to understand. Right now it seems you just need to select by av.sku_id:

select av.value, av.id, av.sku_id from product_attr_vals where av.sku_id = '#3103513504350';

But I suppose the value you were referring, i.e. 12.7oz, is saved somewhere, from the above I don't understand where this is.

Also when you talk about tabs are you referring to the result set or to a pagination?

[hschillig]

Yeah we won't know what the SKU is.. so I just separated it into two queries. Because the sku_id is in the same table as the other values so putting where sku_id = '#202002033' will return no results.

Tabs as in jQuery tabs.. so a result set.