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