CODEWITHSUNDEEP
sql
kyusan93
kyusan93

Reputation: 422

SQL query to get the correct results

I am having issue trying to get the below select work. Can anyone help? Thanks.

SELECT 
        i.InventoryID,i.AttributeSKUID,s.AttributeValue
    FROM 
        ma_product_inventory i
    LEFT JOIN
        ma_product_attribute_sku s
    ON
        i.AttributeSKUID=s.AttributeSKUID
    LEFT JOIN
        ma_product p
    ON
        p.ProductID=s.ProductID
    WHERE 
        s.ProductID='1'
    AND
        (
        s.AttributeValue='xs'
        OR
        s.AttributeValue='green'
        )

I am getting the below results:

INVENTORYID ATTRIBUTESKUID  ATTRIBUTEVALUE
1   1   xs
1   1   green
2   2   green
3   3   green
4   4   green
5   5   green
6   6   xs

How can i get the AttributeSKUID?

Thanks.

Upvotes: 0

Views: 83

Answers (4)

kyusan93
kyusan93

Reputation: 422

Select s.AttributeSKUID 
from
ma_product_attribute_sku s
LEFT JOIN
ma_product p
ON
p.ProductID=s.ProductID
WHERE 
s.ProductID='1'
AND
(s.AttributeValue='xl'
OR
s.AttributeValue='red')
Group by 
S.AttributeSKUID
having count(*) = 2

Upvotes: 0

Androidz
Androidz

Reputation: 423

try adding distinct at kyusan93 code Select distinct(s.attribute),i.inventoryId,s.attributevalue group by S.attributeSKUID having count (*) =2 - this will get all attributeSKUID repeated twice. Hope that will help you.

Upvotes: 2

Þaw
Þaw

Reputation: 2057

try using SELECT DISTINCT and why did you join to ma_product p? It seems you didn't use that table.

Upvotes: 2

cahen
cahen

Reputation: 16636

Add this at the end of your query GROUP BY s.AttributeValue

Upvotes: 2

Related Questions