views:

38

answers:

2

I'm trying to get all products from the database with a single query. I get stuck at the price part:

VirtueMart has a table called #__vm_product and another one called #__vm_product_price.

If a product has a parent product, it means that product inherits everything from the parent unless it's set different in the child.

The tables look like this:

/* #__vm_product PARTIAL */
int - product_id
int - product_parent_id
varchar - product_name

/* #__vm_product_price PARTIAL */
int - product_id
decimal - product_price
int - mdate

I made the next query which gets all products with their price:

SELECT
p.product_id AS id,
product_name AS name,
product_price AS price,
p.product_parent_id AS parent,
MAX(pp.mdate) AS last_updated

FROM jos_vm_product p
LEFT JOIN jos_vm_product_price pp ON p.product_id = pp.product_id

GROUP BY p.product_id
ORDER BY p.product_id

The problem with this query is that it doesn't check if their is a price specified. So if it's a child product and it has no price, it should show the price of it's parent.

Could someone help me out with this?

Note: If anyone knows an easier way to get all products (with price) from the VirtueMart database, please don't mind to tell me :)

EDIT: Price is never null. If child is supposed to inherit from it's parent it just doesn't have a price row in jos_vm_product_price

+1  A: 

You can use a CASE statement to check the product price for the child and if it's null, use the parent price.

Also, you need another join to get the parent.

    SELECT
        p.product_id AS id,
        product_name AS name,
        CASE
            WHEN pp.product_price IS NULL then pp2.product_price
            ELSE pp.product_price
        END AS price,
        p.product_parent_id AS parent,
        MAX(pp.mdate) AS last_updated

    FROM jos_vm_product p
    LEFT OUTER JOIN jos_vm_product_price pp
           ON p.product_id = pp.product_id
       , jos_vm_product parent
       , jos_vm_product_price pp2
   WHERE (p.product_parent_id = parent.product_id OR p.product_parent_id IS NULL)
     AND (parent.product_id = pp2.product_id OR p.product_parent_id IS NULL)
    GROUP BY
        p.product_id,
        product_name,
        CASE
           WHEN pp.product_price IS NULL then pp2.product_price
           ELSE pp.product_price
        END,
        p.product_parent_id
dcp
That won't work because product_price does not exist in jos_vm_product
MrSoundless
@MrSoundless - Yes, I discovered that after looking closer. Check the latest edit. I haven't tested it, so there may be some syntax errors.
dcp
Thanks!! This worked after a couple of small fixes (parent_product_id should be product_parent_id and p.parent_product_id = 0 instead of p.parent_product_id IS NULL).
MrSoundless
My mistake, it doesn't seem to work. It doesn't return the rows where price is not specified. The reason is probably because price is never null. There is no row in jos_vm_product_price for the child if it's supposed to inherit from it's parent. (I guess I should have mentioned this before)
MrSoundless
@MrSoundless - Ok, in that case you need to use left outer join since a child price record might not be present. See the latest edit and let me know how it goes.
dcp
It showed all prices for each product (old prices are stored too) but with a bit of editing it's perfect now: http://pastebin.org/448897Thank you very much for all your effort!
MrSoundless
+1  A: 

Updated:

select
    p.product_id AS id, 
    p.product_name AS name, 
    coalesce(pp.product_price, pp2.product_price) AS price, 
    p.product_parent_id AS parent, 
    coalesce(pp.mdate, pp2.mdate) AS last_updated 
from jos_vm_product p 
left outer join jos_vm_product p2 on p.product_parent_id = p2.product_id
left outer join (
    select product_id, max(mdate) as maxmdate
    from jos_vm_product_price
    group by product_id
) ppm on p.product_id = ppm.product_id
left outer join jos_vm_product_price pp on ppm.product_id = pp.product_id and ppm.maxmdate = pp.mdate 
left outer join (
    select product_id, max(mdate) as maxmdate
    from jos_vm_product_price
    group by product_id
) ppm2 on p2.product_id = ppm2.product_id
left outer join jos_vm_product_price pp2 on ppm2.product_id = pp2.product_id and ppm2.maxmdate = pp2.mdate 
RedFilter
I'm not sure how to fix this query. It also doesn't use jos_vm_product_price which makes it harde for me to find out which one is supposed to be the price table.
MrSoundless
Sorry, it only returns the child products. Also please read the EDIT note in the main post because I made a 'small' mistake in my explanation.
MrSoundless
See my update. I assumed from your earlier text that all products you wanted had parents.
RedFilter
I still get the same results.
MrSoundless
Updated, give it another try.
RedFilter
Another perfect query. Works like a charm. Thank you for all your effort.
MrSoundless