Howto use SQL DISTINCT on this?

[eluser]Clooner[/eluser]
I've have this query and I'm a bit lost on where to go.

Code:

$sql = "(SELECT AVG(review.rating) FROM review ".
       "WHERE product.product_id = review.product_id ".
       "GROUP BY review.product_id) AS rating";
              
$this->db->select('product.*');

$this->db->select('product_to_category.*');
$this->db->select('product_description.*');

$this->db->select('product_description.name AS name');
$this->db->select('product.image');
$this->db->select('manufacturer.name AS manufacturer');
$this->db->select('stock_status.name AS stock');
$this->db->select($sql, FALSE);
        
$this->db->join('product_description', 'product.product_id = product_description.product_id', 'left');
$this->db->join('product_to_store', 'product.product_id = product_to_store.product_id', 'left');
$this->db->join('manufacturer', 'product.manufacturer_id = manufacturer.manufacturer_id', 'left');
$this->db->join('stock_status', 'product.stock_status_id = stock_status.stock_status_id', 'left');
$this->db->join('product_to_category', 'product.product_id = product_to_category.product_id', 'left');
        
$this->db->where('product.status', TRUE);
$this->db->where('product.date_available <=NOW()');
$this->db->where('product_description.language_id', $language_id);
$this->db->where('product_to_store.store_id', $store_id);
$this->db->where('stock_status.language_id', $language_id);

$this->db->where_in('product_to_category.category_id', $category_id);
        
$sort_data = array('LCASE(product_description.name)','product.price','rating');    
            
if (in_array($sort, $sort_data))
    $this->db->order_by($sort, $order);

$this->db->order_by('viewed', 'DESC');
                
$query = $this->db->get($this->table, $limit, $start);
return $this->result($query);

This query works fine and gets the results I want. Except when I have one product in multiple categories I get that product twice or three times when I really want them just one time. I added the function

Code:

$this->db->distinct('product.product_id');

But it keeps returning the same products multiple times. I figure it is because I also select things from other tables and they also have a product_id so I also added them like so

Code:

$this->db->distinct('basically every table with a.product_id');

This doesn't work. I guess I'm not fully understanding the workings of the DISTINCT function or I am forgetting something. Hopefully someone can explain to me how to correctly use the SQL distinct command

[eluser]mddd[/eluser]
First of all, you can't specify a field with DISTINCT. It is an on/off switch. If you use DISTINCT, Mysql will not give duplicate rows. However, a row is only duplicate of ALL your fields are exactly the same. This is not the case here: row 1 and 2 may have the same product id, but the category is different so the row is not the same!

You should use GROUP BY product.product_id to make sure only the first row of every "group" is shown. The group means: all the rows that have that same product_id as the starting point, even when other columns may be different.

[eluser]Clooner[/eluser]
[quote author="mddd" date="1275675550"]First of all, you can't specify a field with DISTINCT. It is an on/off switch. If you use DISTINCT, Mysql will not give duplicate rows. However, a row is only duplicate of ALL your fields are exactly the same. This is not the case here: row 1 and 2 may have the same product id, but the category is different so the row is not the same!

You should use GROUP BY product.product_id to make sure only the first row of every "group" is shown. The group means: all the rows that have that same product_id as the starting point, even when other columns may be different.[/quote]

Of course, stupid me Thanks!