Mysql Query help...

[ajlisowski]

So I need some help trying to develop a query which may not even be possible...

 

Basically I have a number of tables which represent a product. Ill simplify things with dummy tables. I have a product table which contains product information, a product_vendor table which connects a vendor to a product based on region. Then a pricing table which gives prices based on the product_vendor relationship. This allows multiple vendors to set their price per region.

 

 

product

id,

product_sku

product_name,

manufacturer,

 

product_vendor

product_to_vendor_id,

product_sku,

vendor,

region,

 

price

product_to_vendor_id

price

 

The tricky part is, I also have a modifications table. Which allows users to modify product information to be approved by an admin user later.

 

modification

product_sku,

table,

field,

value,

user

 

So it stores what field of the product has been modified and by who.

 

I want to write a query that gets all the product info, but replaced fields with the corresponding modifcation entry if it exists.  Im not sure if a mysql query can get the job done in one go.

 

Perhaps I would need to get all the data, then get all the modifications and run through them replacing the data of the first results with the corresponding value of the modifications table? I would like to do it in one query but im not sure thats possible. Any help would be great..

[jdavidbakr]

Off the top of my head, not sure this will work but maybe this will get you started in the right direction.  (Or maybe it will get you started in the wrong direction...)

 

select if(modification.field = 'product_name' and modification.value is not NULL, modification.value, product.product_name) product_name,
       if(modification.field = 'manufacturer' and modification.value is not NULL, modification.value, product.manufacturer) manufacturer
from product
left join modification on (modification.product_sku = product.product_sku and modification.table = 'product')
group by product.id

 

I'm assuming you're linking the modification to the product and product_vendor tables via the product_sku; the price table would be a bit more complex but not too much, if the above query works it shouldn't be too hard to extend it to work for the price table.