I want to show all the categories and the items inside that each category which should store in items_this_category.

Note, I have created an associative table item_categories because one item can be listed in more than one category.

Here is a screen of NaviCat with structure and the query

enter image description here

  • Why do people downvote if you dont have answer??? – Umair May 27 '14 at 17:24

What you need can be achieved via a simple combination of two joins, Let's have a look at a sample query

Select < Fields as per your requirements >
From items i
Inner join item_categories ic On i.item_id=ic.item_id
Right outer join categories c On c.category_id=ic.category_id
Where <Any conditions you may have>

I hope this is what you were looking for.

  • 1
    I like this answer because it exposes a flaw in the picture in the question. The arrow goes from categories.category_id to item_categories.category_id. For the picture to match this query, 2 things should be done for visual effect: 1) put the items table on the right and the item_categories table in the middle 2) put an arrow from item_categories.item_id to items.item_id. The query itself is essentially correct. If the picture was oriented the way I said, the query would be more obvious. +1 for Speedypoint. – RolandoMySQLDBA May 28 '14 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.