Distinct Confusion

[wwfc_barmy_army]

Hello,

 

 

$sql = "SELECT DISTINCT products.product_id, category_matchup.match_cat_id, products.product_name
FROM category_matchup
INNER JOIN products
ON category_matchup.product_id = products.product_id
WHERE match_cat_id IN (1870,1871,1872,1875,1880,1881,1882,1883,1884)";
    	$result = mysql_query($sql, $connex) or die(mysql_error());
    if(mysql_num_rows($result)!=0){
$i = 0;
     while($row = mysql_fetch_array($result))
{
.................. Output etc..................

 

 

It works, but despite the distint still being in there it is returning multiple records.

 

I have 2 tables for this Product table and a match up table as 1 product can be in many categories. So an example of the matchup table could be:

 

match_id

product_id

cat_id

1

12

532

2

12

535

3

12

356

4

12

36

5

12

3436

6

13

3436

7

15

3436

 

For example if I looked for products in category 3436 AND 36 it will return product ID 12 twice (based on the above example).

 

Can anyone shed any light on this?

 

Thanks.

[gizmola]

The distinct is applied to all the columns in your list:  SELECT DISTINCT products.product_id, category_matchup.match_cat_id, products.product_name.  So in other words you get every distinct combination of product_id, match_cat_id, product_name in the results.

[wwfc_barmy_army]

How do I return more than 1 value back from the query if I only wanted to select a distinct product_id? Thanks.

[gizmola]

How do I return more than 1 value back from the query if I only wanted to select a distinct product_id? Thanks.

 

Your question doesn't make any sense.  I'm going to guess that what you are really asking is, how can you make the results Distinct only on the basis of the product_id.

 

The answer is: you can't with DISTINCT, and quite simply it doesn't make any sense to expect that the database should arbitrarily exclude rows from a result based on no actual criteria, which when you come right down to it, is what you are asking mysql to do.  The way to achieve this is to apply a group by on the column, although again, I feel it important to point out that the result set will be arbitrary in the sense that the values for the other columns will be completely arbitrary.

 

Add a GROUP BY products.product_id to the end of the query.

[wwfc_barmy_army]

Thanks.