Join Category and Subcategory in Same Table

mysql query for same table category and subcategory with separated

You query joins are OK, selecting right columns will do.

SELECT c1.categoryId, CONCAT((CASE WHEN c2.categoryId IS NOT NULL THEN CONCAT(c2.categoryName, ' > ') ELSE '' END), c1.categoryName) categoryName, c1.categoryType, c1.parentCategoryId, c1.status 
FROM categories c1 
LEFT JOIN categories c2 ON c2.categoryId = c1.parentCategoryId

It assumes your category structure is parent-sub two levels only.

INNER JOIN same table category and subcategory

I think you want LEFT JOIN (which shows rows for which parent didn't get a match).

It also seems that you want to match on id_lingua as well.

SELECT cat.*, parent.categoria as nome
FROM categorias cat
LEFT JOIN categorias parent
ON cat.nivel = parent.id_registro
  AND cat.id_lingua = parent.id_lingua
WHERE cat.id_lingua = 2

You may also want to add IFNULL, since parent.* will be NULL for no match and it looks like you want Empty to be displayed: (unless you meant an actual empty string)

SELECT cat.*, IFNULL(parent.categoria, 'Empty') as nome
FROM categorias cat
LEFT JOIN categorias parent
ON cat.nivel = parent.id_registro
  AND cat.id_lingua = parent.id_lingua
WHERE cat.id_lingua = 2

This answer has a nice picture explaining the difference between different joins:

MySql select query for same table category and subcategory parent under child

This one could work

SELECT c1.* 
FROM categories c1 
LEFT JOIN categories c2 on c2.categoryId = c1.parentCategoryId
ORDER BY COALESCE(c2.categoryId, c1.categoryId), c1.categoryId

Edit

If root category is fixed 0, it can be simpler without JOIN

SELECT *
FROM categories
ORDER BY CASE WHEN parentCategoryId = 0 THEN categoryId ELSE parentCategoryId END, categoryId

Select categories and sub-categories from the same table

This is simple join, since you have only 1 level of hierarchy:

select p2.Name as Package, 
       p1.Name as [Product(Loose)]
from Hierarchy h
join Products p1 on h.ID = p1.ID
join Products p2 on h.ParentID = p2.ID

SQL LEFT JOIN to many categories

Totally separate answer because of the change to older technology. I think my original answer is still good for folks using current SQL Server versions, so I don't want to remove it.

I don't remember where I got the function. When I found it today it was named split_delimiter. I changed the name, added some comments, and incorporated the ability to have a delimiter that is more than one character long.

CREATE FUNCTION [dbo].[udf_split_string](@delimited_string VARCHAR(8000), @delimiter varchar(10))
RETURNS TABLE AS
RETURN
WITH cte10(num) AS (    --  10 rows
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)                         
, cte100(num) AS (  --  100 rows
    SELECT 1 
    FROM   cte10 t1, cte10 t2
)
, cte10000(num) AS (    --  10000 rows
    SELECT 1 
    FROM   cte100 t1, cte100 t2
)
, cte1(num) AS (    --  1 row per character
    SELECT TOP (ISNULL(DATALENGTH(@delimited_string), 0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
    FROM   cte10000
)
, cte2(num) AS (    --  locations of strings
    SELECT  1
    UNION ALL
    SELECT t.num + len(replace(@delimiter, ' ', '_'))
    FROM   cte1 t
    WHERE  SUBSTRING(@delimited_string, t.num, len(replace(@delimiter, ' ', '_'))) = @delimiter
)
, cte3(num, [len]) AS (
    SELECT t.num
         , ISNULL(NULLIF(CHARINDEX(@delimiter, @delimited_string, t.num), 0) - t.num, 8000)
    FROM  cte2 t
)

SELECT [Key]   = ROW_NUMBER() OVER (ORDER BY t.num)
     , [Value] = SUBSTRING(@delimited_string, t.num, t.[len])
FROM cte3 t;
GO



DECLARE @PRODUCTS TABLE (ID int, DESCRIPTION varchar(50), CAT varchar(30), SUBCAT varchar(30), SUBSUBCAT varchar(30));

INSERT @PRODUCTS (ID, DESCRIPTION, CAT, SUBCAT, SUBSUBCAT) VALUES
(1, 'NIKE MILLENIUM', '1, 2', '10, 12', '100, 135'),
(2, 'NIKE CORTEZ', '1, 5', '12, 15', '104, 374'),
(3, 'ADIDAS PANTS', '2, 6, 1', '27, 35, 10', '238, 374, 100'),
(4, 'JOE THE PLUMBER JEANS', '1, 5', '27', '238, 374');

DECLARE @CATS TABLE (ID int, DESCR varchar(100));

INSERT @CATS (ID, DESCR) VALUES
(1, 'MEN'),
(2, 'WOMEN'),
(3, 'UNISEX'),
(4, 'KIDS'),
(5, 'TEENS'),
(6, 'BACK TO SCHOOL');

DECLARE @SUBCATS TABLE (ID int, DESCR varchar(100));

INSERT @SUBCATS (ID, DESCR) VALUES
(10, 'FOOTWEAR'),
(12, 'OUTERWEAR'),
(14, 'SWIMWEAR'),
(15, 'HOODIES'),
(27, 'CLOTHING'),
(35, 'SPORTS');

DECLARE @SUBSUBCATS TABLE (ID int, DESCR varchar(100));

INSERT @SUBSUBCATS (ID, DESCR) VALUES
(100, 'RUNNING'),
(104, 'ZIP TOPS'),
(135, 'FLEECE'),
(157, 'BIKINIS'),
(238, 'PANTS'),
(374, 'JOGGERS');

;
with prod as (
    SELECT p.ID,
        p.DESCRIPTION
        , c.value as CatId
        , c.[key] as CatKey
        , sc.value as SubCatId
        , sc.[key] as SubCatKey
        , ssc.value as SubSubCatId
        , ssc.[key] as SubSubCatKey
    FROM @PRODUCTS p
      cross apply dbo.udf_split_string(cat, ', ') c
      cross apply dbo.udf_split_string(subcat, ', ') sc
      cross apply dbo.udf_split_string(subsubcat, ', ') ssc
    where c.[key] = sc.[key]
      and c.[key] = ssc.[key]
)
, a as (
    select p.ID
    , p.DESCRIPTION
    , c.DESCR + ' > ' + sc.DESCR + ' > ' + ssc.DESCR as CATEGORIES
    , p.CatKey
    from prod p
      inner join @CATS c on c.ID = p.CatId
      inner join @SUBCATS sc on sc.ID = p.SubCatId
      inner join @SUBSUBCATS ssc on ssc.ID = p.SubSubCatId
)

select DISTINCT ID
, DESCRIPTION
, replace(STUFF((SELECT distinct ' | ' + a2.CATEGORIES
            from a a2
            where a.ID = a2.ID
            FOR XML PATH(''))
        ,1,2,''), '>', '>') CATEGORIES
from a

Obtaining categories and subcategories joined from Mysql tables

As long as you only have two levels of categories (e.g. no sub-subcategories), then using a JOIN can be fine.

The problem is, your data sometimes references parent categories, and sometimes references subcategories. That's why you're getting mixed results.

To avoid mixed results, you can use something like this:

SELECT
  p.*,
  CONCAT(IFNULL(GROUP_CONCAT(c_parent...), ''), ',', IFNULL(GROUP_CONCAT(c_direct_parent...), '')),
  GROUP_CONCAT(c_child...),
FROM `places` AS p
  LEFT JOIN `places_categories_rel` AS rel
    ON rel.`place_id` = p.`id`
  LEFT JOIN `categories` AS c_direct_parent
    ON c_direct_parent.`id` = rel.`category_id`
    AND c_direct_parent.`parent_id` IS NULL
  LEFT JOIN `categories` AS c_child
    ON c_child.`id` = rel.`category_id`
    AND c_child.`parent_id` IS NOT NULL
  LEFT JOIN `categories` AS c_parent
    ON c_parent.`id` = c_child.`parent_id`
GROUP BY p.`id`

I'm not sure this is a very efficient option, but as you can see, you need to separate out "related categories that are parents" (c_direct_parent) and "related categories are are children" (c_child).

With that separation, you can now properly retrieve a list of data for the parents and the children. For clarity, I put "..." for the GROUP_CONCAT internals.

Categories and subcategories from one mysql table

You can use some kind of recursive function like this example:

// Recursive function
function getList($rows, $id=0){

    // Start a new list
    $newList = null;

    foreach($rows as $key => $row) {
        if ($row['parent_id'] == $id) {
            // Add an UL tag when LI exists
            $newList == null ? $newList = "<ul>" : $newList;
            $newList .= "<li>";
            $newList .= "<a href=\"\">{$row['category']}</a>";
            // Find childrens
            $newList .= getList(array_slice($rows,$key),$row['id']);
            $newList .= "</li>";
        }   
    }   
    // Add an UL end tag
    strlen($newList) > 0 ? $newList .= "</ul>" : $newList;

    return $newList;
}

// Your database registers
$rows = [
    ['id'=>1,'category'=>'Indland','parent_id'=>0],
    ['id'=>2,'category'=>'Udland','parent_id'=>0],
    ['id'=>3,'category'=>'Sport','parent_id'=>0],
    ['id'=>4,'category'=>'Handbold','parent_id'=>3],
    ['id'=>5,'category'=>'Fodbold','parent_id'=>3],
    ['id'=>6,'category'=>'Sample','parent_id'=>4]
];

print_r(getList($rows));

But it only works with ordered rows.

---

Related Topics