MySQL Forums
Forum List  »  PHP

How to use a distinct with an innerjoin
Posted by: Pim Duijst
Date: March 27, 2012 08:31AM

Hello everyone,

I am pretty new to this forum so I'm sorry if I ask something which should be pretty easy.

I am using a CodeIgniter-installation to write an API.

I have the following two tables:

product_category: id title store_id position visible
product_subcategory: id title category_id store_id position visible

where category_id in product_subcategory is the unique id from product_category.

Now I have this query:

SELECT product_category.id, product_category.title AS name, product_subcategory.id AS subcat_id, product_subcategory.title AS child_category, product_subcategory.category_id
FROM product_category
LEFT JOIN product_subcategory
ON product_subcategory.category_id = product_category.id
ORDER BY product_category.id ASC

which shows me this result:

id name subcat_id child_category category_id
23 Fun & Games 351 Bugaboo Donkey 23
23 Fun & Games 452 Philips Avent verzorgingsproducten 23
23 Fun & Games 436 Moepa 23
24 Girls Gifts NULL NULL NULL

Now what I want is to distinct the id and name when a category has multiple subcategories. Can this be done and how? I am struggling with this for more than 8 hours so is there please anyone who can help me? Ask me if you need more info or if you want an export of these 2 tables to test it yourself :)

Kind regards,

Pim Duijst

Options: ReplyQuote


Subject
Written By
Posted
How to use a distinct with an innerjoin
March 27, 2012 08:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.