Limit Problem in JOIN

[cmarlo]

I'm having difficulty getting the right number of results with LIMIT and JOIN in my PHP script.

Table one "productline" contains basic information about my products. Table two "productline_images" lists all the images associated with each product.

Here is my problem. I basically want to return 10 products from table 1 with all it's associated images from table 2. However if a product has 10 images, it's only going to return 1 record from table one.

I have a nice loop that breaks my HTML table accordingly, so everything is horizontal, regardless of how many columns is needed for images. I've included the code. Hope this makes sense. My thanks to anyone who can help.

<?php
$display = 10;
$start = 1;

	$query = "SELECT p.style_id, p.style, p.description, i.thumbnail, i.style
	FROM productline p, productline_images i
	where p.style_id = i.style_id LIMIT $start, $display";


	$result = mysql_query($query);

	$rows = mysql_num_rows($result);

	$displayhtmltableintro = '1';

	for ($i=0; $i < $rows; $i++) {
	// Get individual record
	$Products = mysql_fetch_array($result);
	$style=$Products['style'];
	$thumbnail=$Products['thumbnail'];

	if ($checkstyle != $style)	{	
	echo '</tr></table>';
    $displayhtmltableintro = 1;
	} 

	if ($displayhtmltableintro == 1) {
    echo '<table><tr>';
    $displayhtmltableintro = '0';
    } 

    echo '<td width="150" align="left" bgcolor="#ffffff"><p><b>Style_ID:'.$style.' <br />'.$thumbnail.'</p></td>';

    $checkstyle = $style;

    }

?>

[Piranha]

You need to use subqueries for this. Something like this should work:

SELECT p.style_id, p.style, p.description, i.thumbnail, i.style
FROM productline p
LEFT JOIN productline_images i ON p.style_id = i.style_id 
WHERE p.style_id in (
   SELECT p.style_id
   FROM productline p
   LIMIT $start, $display
)

[cmarlo]

Thank you so much. My mind was spinning on this. I'll give it a try.