SQL与INNER JOIN不同

Question

I have problem in options If I embed DISTINCT in query I have value= "". If I remove DISTINCT I have value= "1" and I need this.

 $query_result = mysqli_query($db, "SELECT DISTINCT C.name FROM Category AS C INNER JOIN MarketProduct AS MP ON C.Category_ID = MP.ID_Category WHERE MP.ID_Market ='$id'");

<select name="ID_Category">
<?php
while($row = mysqli_fetch_array($query_result)){   
?>

<option value="<?php echo $row['ID_Category'] ; ?>"><?php echo $row['name'] ; ?></option>


<?php
}
?>
</select>

Answer 1

Instead of DISTINCT, use GROUP BY. See below:

  $query_result = mysqli_query($db, "SELECT C.name FROM Category AS C INNER JOIN MarketProduct AS MP ON C.Category_ID = MP.ID_Category WHERE MP.ID_Market ='$id' GROUP BY C.name");