How do I use DISTINCT inside INNER JOIN

[klemme]

Hi,

I want to only compare with a distinct value in a join across two tables.

I am bulding a navigation, based on what gets returned (Based on how many "brand_id" is found in the join.) - I just only need to know if there is ONE brand_id of a kind in the table joined (brand_id = 1 or 3 or etc etc.)

This is the sql I have now, but it returns two or more rows if the brand_id is found more than one time, and then my navigation has i.e. 3 of the same links if that was the number of brand_id found in the JOIN.

Here is is:

$sql = mysqli_query($connection, "
SELECT * FROM brands INNER JOIN products ON brands.brand_id = DISTINCT products.brand_id WHERE products.stock > 0");

--COMMENT - Now nothing gets returned, because of the wrong use of DISTINCT???
--How can I implement that in the query which is simple afterall :-)
				
while ($row = mysqli_fetch_assoc($sql))
{
echo '
<li>
<a href="lager.php?brand_id='.$row['brand_id'].'" style="color:#069;"> - '.$row['navn'].'</a>
</li>';
}

[StephNicolaou]

I would advise you to try and place the DISTINCT element at the beginning of the statement and work from there, e.g.

SELECT DISTINCT brands.brand_id FROM brands INNER JOIN products ON brands.brand_id = products.brand_id WHERE products.stock > 0"

Alternatively, remove the inner join and use the match in the WHERE statement.

SELECT DISTINCT brands.brand_id FROM brands, products WHERE products brands.brand_id = products.brand_id AND products.stock > 0"

Hope that helps