Hi Jason, hmm I see your point about that inner join needs both records matching...it seems that left outer join doesnt work either.
I was wondering in your SQL your using FROM Products first...(but since that products and feedback are both equals in this scenerio wouldnt I need to start with SELECT featured (and then use a join for both products and feedback tables to connect to featured table).
I have previously tried different versions of SQL but without luck.
this is currently my SQL
$basefile = basename ( $_SERVER["REQUEST_URI"] );
mysql_select_db($database_dinkydb, $dinkydb);
$query_rsFeatured = "SELECT * FROM products LEFT OUTER JOIN featured ON products.ProductID = featured.FeaturedTypeID LEFT OUTER JOIN feedback ON feedback.FeedbackID = featured.FeaturedTypeID WHERE FeaturedLocale = '$basefile'";
$rsFeatured = mysql_query($query_rsFeatured, $dinkydb) or die(mysql_error());
$row_rsFeatured = mysql_fetch_assoc($rsFeatured);
$totalRows_rsFeatured = mysql_num_rows($rsFeatured);
As stated before I was told the only way to solve this is to use sub-queries - but i havent had much expeirence in this.
Side Note: to be clear the 2 tables Products and Feedback have no relationship with each other. All im doing is wanting to mix up the displaying of random products and random feedback (which IDs are are stored in the Featured table) so the only way to do this is to stored product ids and feedback ids in one single table so thay when i do a recordset repeat region i can have them displaying randomly.
I was told that: Sub queries can only bring back 1 row on the sub query but are great for calculating a total.
A sub query on a join will behave like a union query but the fields don't have to match. It will return you all the rows from both tables.
The alternative is to have one field brought back as an array of all records as a coma separated list and simply loop over that list.
So I think the way to solve this issue is to use a sub query, but in context to my tables as noted above I dont know how.
Any other ideas?
Chris
