join, left or right... :S

Hi Ray

Am trying to get a join in a recordset so that I am able to shwo the contents of the main table whilst also being able to show 1 column from 2 other tables.

The RS I have (and it isnt working) is

<?php
$FeaturedProductPanel = new WA_MySQLi_RS("FeaturedProductPanel",$DBConnection,1);
$FeaturedProductPanel->setQuery("SELECT products.*, productmanufacturers.*, productcategories.* FROM products RIGHT OUTER JOIN productmanufacturers ON products.ProductManufacturerID = productmanufacturers.ManufacturerName RIGHT OUTER JOIN productcategories ON products.ProductCategoryID = productcategories.CategoryName WHERE products.ProductLive = 1 ORDER BY Rand()");
$FeaturedProductPanel->execute();
?>

What it is supposed to be doing is showing the product from the products table and then the CategoryName from the productcategories table and ManufacturerName from the productmanufacturers table that is relevant to the product itself...

Not sure where or what I have done wrong.

Cheers

Jamie

I think you would want to use LEFT or INNER joins and not RIGHT joins, since the products is the main table you want to always return results from.

Then I think you may be joining on the wrong columns... for example you have: products.ProductManufacturerID = productmanufacturers.ManufacturerName

shouldn't it be: products.ProductManufacturerID = productmanufacturers.ManufacturerID

My guess is that the full query you want is:

SELECT products.*, productmanufacturers.ManufacturerName , productcategories.CategoryName  FROM products INNER JOIN productmanufacturers ON products.ProductManufacturerID = productmanufacturers.ManufacturerID INNER JOIN productcategories ON products.ProductCategoryID = productcategories.CategoryID WHERE products.ProductLive = 1 ORDER BY Rand()

got it, thanks Ray....always getting confused on the joins!