SELECT issue

[rseigel]

I have the following SELECT that almost works:

SELECT product.reference AS sku, product.price - specific_price.reduction AS price, stock_available.quantity, marketplace_product_option.asin1 AS 'product-id', 'ASIN' AS 'product-id-type', product.condition AS 'condition-type'
FROM product, specific_price, stock_available, marketplace_product_option
WHERE product.id_product = specific_price.id_product
AND product.id_product = stock_available.id_product
AND product.id_product = marketplace_product_option.id_product

The problem is that sometimes there is no entry of specific_price.reduction (it's not ZERO - it just doesn't have a row in the table at all for that particular product).

 

What I want to do is if specific_price.reduction exists then do the math (product.price - specific_price.reduction)  otherwise just use product.price as the value.

 

I'd like to do this INLINE in the SELECT rather than resorting to PHP.

 

I hope that makes sense.

 

Ron

[mac_gyver]

you would need to explicitly use a LEFT JOIN between the other tables and the specific_price table, so that there will be a row in the result set even when there isn't one in the specific_price table. then use IFNULL(expr1,expr2) in the select term to get either the product.price - specific_price.reduction or just the product.price (expressions using a NULL value return a NULL.)

[rseigel]

Ok...thanks,. I get what you mean....I just can't get the syntax correct.

 

Any chance of a bit more of a hint?

[mac_gyver]

what have you tried?

you also need to use alias names to shorten the entire query statement.

the select term should (untested) be (assuming alias names of p and sp) -

IFNULL(p.price - sp.reduction,p.price) AS price

 

[rseigel]

ok.....sorted the alias names part....

SELECT p.reference AS sku, p.price - sp.reduction AS price, sa.quantity, m.asin1 AS 'product-id', 'ASIN' AS 'product-id-type', p.condition AS 'condition-type'
FROM product AS p, specific_price AS sp, stock_available AS sa, marketplace_product_option AS m
WHERE p.id_product = sp.id_product
AND p.id_product = sa.id_product
AND p.id_product = m.id_product

Still confused ablut the left join.

 

Do I need 3 left joins in the SELECT?

Edited August 11, 2013 by rseigel

[mac_gyver]

should (untested) work -

SELECT p.reference AS sku, IFNULL(p.price - sp.reduction,p.price) AS price, sa.quantity,
 mpo.asin1 AS 'product-id', 'ASIN' AS 'product-id-type', p.condition AS 'condition-type'
 FROM product p
 LEFT JOIN specific_price sp USING (id_product)
 JOIN stock_available sa USING (id_product)
 JOIN marketplace_product_option mpo USING (id_product)

[rseigel]

Wicked. That works.

 

Now the only thing I need to do is eliminate all records where mpo.asin1 doesn't exist.

[rseigel]

Actually that should have read:

 

Now the only thing I need to do is eliminate all records where mpo.asin1 doesn't exist or is NULL.

[kicken]

Just add that condition to your WHERE clause.

WHERE mpo.asin1 IS NOT NULL

[rseigel]

Thanks for the kick in the right direction.

 

I actually ended up using:

SELECT p.reference AS sku, IFNULL(p.price - sp.reduction,p.price) AS price, sa.quantity, mpo.asin1 AS 'product-id', 'ASIN' AS 'product-id-type', p.condition AS 'condition-type'
 FROM product p
 LEFT JOIN specific_price sp USING (id_product)
 JOIN stock_available sa USING (id_product)
 JOIN marketplace_product_option mpo USING (id_product)
 WHERE mpo.asin1 > ''

which actually eliminates NULL and blank values.

 

Off to the next challenge.....