problem with query !

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

problem with query !

Post 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());
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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?
Post Reply