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 '
<a href="lager.php?brand_id='.$row['brand_id'].'" style="color:#069;"> - '.$row['navn'].'</a>

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.