Problem with JOINs or GROUP BYs I think, I'm fairly new to MySQL

[nickholt1972]

THE PROBLEM: I'm developing an online shop using PHP and MySQL. I've got products and each product might have different sizes, colours, etc
I have two tables: [b]products[/b] and [b]productdetails[/b]

When i list these in my shop I want it to say (e.g.)

[i]Product 1:
Colour: white, Size: small
Colour white, Size: large[/i]

But what i'm getting is

[i]Product 1:
Colour: white, Size: small

Product 1:
Colour white, Size: large[/i]

This is my SQL Query and the PHP to output it:

$result = @mysql_query('SELECT
products.id, products.productid, products.productname,
productdetails.id, productdetails.size
FROM productdetails RIGHT JOIN products ON products.productid = productdetails.productid');

// Display the text of each entry in a table
  while ($row = mysql_fetch_array($result)) {
  echo '<div id="newproduct"><table border="1">' .
'<tr><td>' . $row['id'] . '</td><td>' .
$row['productid'] . '</td><td>' .
$row['productname'] . '</td></tr>' .
'<tr><td>' . $row['size'] . '</td></tr></table></div>';
}

I'm sure its very simple, it usually is. Any help would be greatly appreciated.
Thanks,
Nick

[alpine]

This i believe is one way:

SELECT
  products.id, products.productid, products.productname,
  productdetails.id, productdetails.size
  FROM productdetails, products WHERE products.productid = productdetails.productid

[fenway]

Well, if you flip the tables and do a left JOIN, your first idea will work... but you'll have to have a seen hash.

[nickholt1972]

ok, i have 2 questions in reply to that...

firstly,
are you saying there's a difference between

table1 RIGHT JOIN ON table2

and

table2 LEFT JOIN ON table 1

and secondly,

what's a 'seen hash'?

Many thanks,
Nick.

[fenway]

1 - LEFT JOIN is used by convention, where as RIGHT JOIN shouldn't even exist, and probably is only still around for compatability.

2 - You have to keep track of the products you've already "seen".