Skip to main content

MySQL query to list Items and its categories.There are tables for Items, Categories and Item_category relation. One item may fall in different categories. In the result there should be no repeation of records and Categories should be comma separated.


There is an ITEM table , CATEGORY table and an ITEM_CATEGORY relation table. Items may fall in multiple categories "Item" table has item id and item name.  "cat" table has Category Id and Category Name. There is a third table  to store the item category relation. "Item_cat" table  contains Itm_Id (item id) and cat_Id (category id).


Here the question is how to generate a result like this

ItmName  ##    Category
Item1 ##    Cat1,Cat3
Item2 ##    Cat2
Item3 ##    Cat1,Cat4
Item3 ##    Cat1

Sample MySQL query to list Items and its categories. In this example there are tables for Items, Categories and Item_category relation. One item may fall in different categories. In the result there should be no repeat of records and the categories should be comma separated.


The result should contain Item name and comma seperated category name to which the item belongs to.

Come on let's do it :)




Step1: Create Category table


CREATE TABLE `cat` (  `CId` int(11) NOT NULL AUTO_INCREMENT,  `CName` varchar(10) NOT NULL,  PRIMARY KEY (`CId`))

Structure of category table:
 "cat" table
 1     CId     int(11)    
 2     CName     varchar(20)



data in  "cat" table  ( Item category )
CId     CName
1     SUV
2     Sedan
3     F1 Race
4     MultiAxl



 Step2:Create Item table

CREATE TABLE `item` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `ItmName` varchar(10) NOT NULL,  PRIMARY KEY (`Id`))

Structure of item table:

 "item" table
 1     Id     int(11)    
 2     ItmName varchar(15)



 data in  "item" table
Id     ItmName
1     Maruthi 800
2     Punto
3   XLT Terran
4     Sienna




 Step3:   Create Item category relation table

 CREATE TABLE `Item_cat` (  `Itm_Id` int(11) NOT NULL,  `cat_Id` int(11) NOT NULL)

  "Item_cat" table    ( Item category relation)
 1     Itm_Id     int(11)        
 2     cat_Id     int(11)



data in Item category relation  ( "Item_cat" ) table

Itm_Id     cat_Id
1     1
1     2
2     1
3     1
3     4
2     3



Step 4 : Execute query

 SELECT ItmName, GROUP_CONCAT(CName SEPARATOR ',') as Category FROM (select I.ItmName,C.CName
  from item I
    left join Item_cat CI on (CI.itm_id = I.ID)
    left join cat C on( C.CId = CI.cat_id )) T GROUP BY ItmName






Final Result 
ItmName  ##    Category
Maruthi 800 ##    SUV,Sedan
Punto ##    SUV,F1 Race
Sienna ##    NULL
XLT Terran ##    SUV,MultiAxl   


Ex: here Maruti 80 comes under 2 categores SUV and sedan

Popular posts from this blog

How to apply for a new ration card and what are the documents required?

List of documents required for new ration card application in Kerala and how to apply


Application for a new ration card should be addressed to Taluk Supply Officer (TSO) / City Rationing Officer (CRO) of applicant's residing area.

Primary document required are Residence certificate, Income certificate and incase the applicant's name is in another ration card then he/she should submit the reduction certificate ontained from previous TSO / CRO as proof for removing his/her name from old Ration card.

You can get the new application from your currently residing Taluk Supply Office.

Payback Points - How to redeem - How to merge multiple payback accounts - Block Payback card - Payback customer care

Joomla and Forum Integration - Integrating Forums to Joomla

Joomla is one of the most popular CMS opensource packages. It is very easier to develop website's using Joomla. You just need to download Joomla package from Joomla's Official website www.joomla.org  and install it on your domain and later adding customizations to templates and feature and Your website is ready :). Now a days most websites provides a forum section for it users for discussing various article topics, gathering opinions etc.



Following are some best know forum opensource packages which can be integrated with Joomla and create a new forum experience for users


Urgent Openings for PHP trainees, Andriod / IOS developers and PHP developers in Kochi Trivandrum Calicut and Bangalore. Please Send Your updated resumes to recruit.vo@gmail.com   Read more »
Member
Search This Blog