Only get unique rows

[xiao]

I have this query:

$result = mysql_query("SELECT p.products_id, p.products_model, p.products_price, p.products_image, pd.products_name, pc.categories_id FROM products p, products_description pd, products_to_categories pc WHERE pc.categories_id = 10 AND pd.language_id = 1 AND p.products_price <=50 ORDER BY p.products_price") or die(mysql_error());

 

What it should do:

Get id, model, price, image and name for all products with category 10 that have a price <= 50

 

I'm using it in osCommerce so I had to include the language_id part to only get the english ones.

But now with the above query I get product like 5 or 6 times (might be more), and I don't know how to fix that...

I tried some GROUP BYs, but that seems to give incorrect rows.

[xiao]

This seems to work a little better:

$result = mysql_query("SELECT p.products_id, p.products_model, p.products_price, p.products_image, pd.products_name, pc.categories_id FROM products p, products_description pd, products_to_categories pc WHERE pc.categories_id = 10 AND p.products_id = pd.products_id AND pd.language_id = 1 AND p.products_price <=50 GROUP BY pd.products_name ORDER BY p.products_price") or die(mysql_error());

 

But it appears to give products with any category_id

[Barand]

is this an improvement?

$result = mysql_query("SELECT DISTINCT p.products_id, p.products_model, p.products_price, 
        p.products_image, pd.products_name, pc.categories_id 
        FROM products p, products_description pd, products_to_categories pc 
        WHERE pc.categories_id = 10 
            AND pd.language_id = 1 
            AND p.products_price <=50 
        ORDER BY p.products_price") 
        or die(mysql_error());

[Barand]

Whoa!

 

I just noticed you have no join criteria specified

 

$result = mysql_query("SELECT DISTINCT p.products_id, p.products_model, p.products_price, 
        p.products_image, pd.products_name 
        FROM products p
        INNER JOIN products_description pd ON p.products_id = pd.products_id
        WHERE p.categories_id = 10 
            AND pd.language_id = 1 
            AND p.products_price <=50 
        ORDER BY p.products_price") 
        or die(mysql_error());

[xiao]

That looks a lot better indeed,

but I need to get the categories_id from products_to_categories.

Right now it gives this error:

Unknown column 'p.categories_id' in 'where clause'

[fenway]

Should be pc.categories_id.