Page 1 of 1

problem with query !

Posted: Thu Dec 15, 2005 1:37 am
by itsmani1
here is my current query which is working fine.....
its basically getting products from product table and currency from currency table.
now i want to get some more products to it.
i want to select product which are in 3rd table named "catrel" where i have got catid and product id
now if the catid = 99 it shuld also select all those products.
any help.................

Code: Select all

mysql_query("
SELECT products.productID as productID,productName,image1,discounted,discountPercentage,price,displaySymbol FROM products,productprice,currency where productprice.currencyid=currency.currencyid and products.productID=productprice.productID and products.categoryID=99 and products.status=1 and productprice.currencyID=1 order by products.productID desc")  or die(mysql_error());

Posted: Fri Dec 16, 2005 3:40 am
by onion2k
If I understand your question corrected you should be able to just drop in a left join to select anything in the third table.

Code: Select all

SELECT 
products.productID as productID, productName, image1, discounted, discountPercentage, price, displaySymbol 
FROM products, productprice, currency 
left join catrel on catrel.catid = '99' and catrel.productID = products.productID
where 
productprice.currencyid=currency.currencyid and 
products.productID=productprice.productID and 
products.categoryID=99 and 
products.status=1 and 
productprice.currencyID=1 
order by products.productID desc
EDIT: Reading your question again I'm not certain this answers it. Could you write it in a simpler way? Perhaps you could give us the table structure?