joining few tables

i want to display both records on either member's favourite and non favourite items

like

product A [tick]
product B []
product C [tick]
..

if people like the product and it's stored as their favourite product [tick will show beside the product]

database using
product: id, product name
favourite: product_id, member_id

please help..
noven

Hi

I shall try to answer this in PHP/MySQL

assuming userid is stored in member_id

mysql_connect($host,$UserName,$Password) ;
mysql_select_db("mydatabase");
$query="select * from product";
$result=mysql_query($query);
while($row =mysql_fetch_array($result))
{
echo $row['product name'];
$product_id = $row['id']
$query1="select * from favourite where product_id = " . $productid . " and member_id = ". $member_id;
$result1=mysql_query($query1);
if mysql_num_rows($result1) <> 0
{
echo "[Tick]";
}
}

Hope this helps

Cheers
Jayesh Jain

i know, but it will make the server so busy

let say if i have 10 listing records per page
and it will do 11 queries.... per page

what i need is i just want 1 query which can do the whole lots

but thanks for your effort anyway..
noven

not sure if this is the optimal way to do it, but here's something you can try out

SELECT p.id, p.name, count(f.product_id) AS checked FROM products AS p LEFT JOIN favourites AS f ON p.id = f.product_id AND f.member_id = $member_id GROUP BY p.id

btw, I'm using left join so that no rows will be left out even if there weren't a match (like you said, you wanted to display the non checked products too)

if you want to display only the checked products, just replace LEFT JOIN with INNER JOIN

hmm..

it's good...

is it possible to have more than 2 tables using left join?
like table a, table b, table c ...

if so how to write the statement?

thanks a lots
noven

I found the solution

don't need to worry AS Below:

SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key