CODEWITHSUNDEEP
sqlmysql
Russell Dias
Russell Dias

Reputation: 73282

Getting products from SQL query

I'm currently working on a proprietary shopping cart system and was having a few problems with getting products out with the correct pricing.

Basically my table structure is as follows:

Products table: (Only relevant columns are represented)

----------------------------------------------------
productid | product | descr | disporder| list_price|
----------------------------------------------------
    1        name      desc      1          0.00
    2        name      desc      4          0.00
    3        name      desc      2          2.45

Pricing table:

----------------------------------------
priceid | productid | price | variantid|
----------------------------------------
    1         1       13.91      1
    2         2       54.25      4
    3         2       47.23      2

Variants Table:

-------------------------------
variantid | productid | active|
-------------------------------
    1          1          Y     
    2          2          Y
    3          2          Y

So, each product can have - and in most cases does have - multiple variants. My current SQL query I have managed to create thus far is:

    SELECT 
        products.productid, product, descr, p.price, i.image_path
    FROM 
        products
    LEFT JOIN 
        pricing AS p 
    ON  
        p.variantid = (SELECT variantid FROM variants 
            WHERE productid = products.productid LIMIT 1)
    LEFT JOIN 
        images_T AS i 
    ON
        i.id = products.productid
    GROUP BY
        products.productid
    ORDER BY
        products.disporder

However, my problem arises when a product does not have a variant. If a product does not have a variant associated with it, the price will be in the list_price column of the products table. How would I go about performing a check to see if a product does indeed have a variant. If not, it should effectively bypass the variants table and get the pricing from list_price within the products table.

Upvotes: 0

Views: 574

Answers (3)

littlegreen
littlegreen

Reputation: 7420

Yes, CASE is an option, or COALESCE:

SELECT 
    products.productid, product, descr, 
    COALESCE(products.list_price, p.price) AS price, 
    i.image_path
...

Just join both prices and when the first is NULL the other will be selected.

Upvotes: 2

DVK
DVK

Reputation: 129363

You can do a full join with variants table (which will ONLY give you producs which have variants), and then UNION it with a join of producs and pricing where there exists no varian (using AND NOT EXISTS (SELECT 1 from variants WHERE p.productid=v.productid and p.variantid =v.variantid)

Otherwise, use CASE on pricing.price

Upvotes: 1

Dan J
Dan J

Reputation: 16708

The simplest way is to use a CASE in the SELECT clause, like so:

SELECT 
    products.productid, product, descr, 
    CASE 
        WHEN p.price IS NULL 
        THEN products.list_price 
        ELSE p.price 
    END AS price, 
    i.image_path
[...]

Since you're left-joining on pricing/variants, p.price should reliably be NULL for products with no variants.

Hopefully that's what you meant by "bypassing" the variants table. :)

Upvotes: 2

Related Questions