[SOLVED] problem with query

[atay]

have 2 tables, products (name, prodID, price) i promotions (prodID, price)

 

and query

SELECT * from products 

 

and I want not change the php code, but I think it can be do in sql. I want join this query with promotions table, but only when there is promotion on article. For example I have:

 

(name, prodID, price)

pencil | 18 | 15.50

 

and now when I have data in promotions (18, 10.50) I want that my query should return

pencil | 18 | 10.50

 

I was trying with JOIN RIGHT, but it returns NULL when article isn't in table promotions.

 

Can anybody help me ?

 

sorry for my english

[fenway]

Try this:

 

SELECT products.name, products.prodID, IFNULL( promotions.price, products.price ) AS price
FROM products
LEFT JOIN promotions ON ( promotions.prodID = products.prodID )

[atay]

works perfect, thanks