MySQL join between two columns with case condition

Question

i have an table with data which has both category and subcategory data in it.

So what is category and sub-category? category is the parent and sub-category is the child. Both data is similar except for one difference, parent_id for sub_category is id of the category.

Sample Data:

What is required?

sub-category name in the result should be category-name +'-'+ sub-category-name

Sample Result:

What is my attempt to solve the problem?

MySQL query which i have returning is done only to extract the sub-category only with the names:

SELECT 
    CONCAT(wcfc1.feed_category_name,
            '-',
            wcfc2.feed_category_name) AS feed_category_name,
    wcfc2.feed_category_id,
    wcfc2.parent_id,
    wcfc2.category_type_id
FROM
    wc_feed_categories wcfc1
        JOIN
    wc_feed_categories wcfc2 ON wcfc1.feed_category_id = wcfc2.parent_id;

This query is showing only:

i'm not able to figure out, how to write a query to show both category and sub-category with the name change in a single query.

Answer 1

Instead of

    JOIN
wc_feed_categories wcfc2 ON wcfc1.feed_category_id = wcfc2.parent_id;

try

    LEFT JOIN
wc_feed_categories wcfc2 ON wcfc1.parent_id = wcfc2.feed_category_id;

so your query will be:

SELECT 
    CASE 
       WHEN wcfc1.parent_id = 0 THEN wcfc1.feed_category_name
       ELSE
            CONCAT(wcfc2.feed_category_name,  -- parent category
               '-', 
               wcfc1.feed_category_name)      -- child category
       END AS feed_category_name,
    wcfc1.feed_category_id,
    wcfc1.parent_id
FROM
    wc_feed_categories wcfc1
        LEFT JOIN
    wc_feed_categories wcfc2 ON wcfc1.parent_id = wcfc2.feed_category_id;

Demo here