Join query problem

[chaitanyajakhad]

Hello,

Some mysql join query problem.
I have two tables. product and material. There is many to many raltion. So I have created other table called products_to_materialcategories for mapping relationship.
Following are the rows.
p1 m1
p1 m2
p2 m3
p3 m2
p3 m4

Now I want to access product, and products_to_materialcategories and find out distinct products, without using distinct keyword, and in a single query.

Can anybody help me.

Query is like following but it do not gives me distinct products list. There are duplicate p3, as p3 is appearing twice in products_to_materialcategories table.

SELECT * FROM products_master a, products_to_materialcategories b WHERE a.products_master_id = b.products_master_id AND a.products_status = 1 AND ( b.materialcategories_id = 5 OR b.materialcategories_id = 3 ) ORDER BY a.products_sort_order

[chaitanyajakhad]

Hey dude,
I think I found the solution.
Please let me know if there is bug in my statement

SELECT a.* FROM products_master a
inner join

products_to_materialcategories b
on a.products_master_id = b.products_master_id
AND a.maincategories_id =1 AND a.products_status = 1 AND ( b.materialcategories_id = 5 OR b.materialcategories_id = 3 )
group by a.products_master_id
ORDER BY a.products_sort_order

Above query and following queries are same.

SELECT distinct a.products_master_id FROM products_master a, products_to_materialcategories b WHERE a.products_master_id = b.products_master_id AND a.products_status = 1 AND ( maincategories_id =1 ) AND ( b.materialcategories_id = 5 OR b.materialcategories_id = 3 ) ORDER BY a.products_sort_order asc

Regards,

Chaitanya Jakhadi

[Roger_Ramjet]

Put your WHERE conditions in a WHERE clause, not in the JOIN. (AND a.maincategories_id =1 AND a.products_status = 1 AND ( b.materialcategories_id = 5 OR b.materialcategories_id = 3 ) is nothing to do with the join since they refer to only one table at a time.