SQL table join on itself but allow “0” value

Okay so i have a simple table “category” on this table categories and subcategories are saved. categories have “parent_id=0”, and subcategories have a “parent_id” that equals the row_id of the category.

for example

ID  name       parent_id
1   fruit      0
2   apple      1
3   pear       1
4   vegetable  0
5   tomato     4

Fruit
-apple
-pear
vegetable
-tomato

In this example ‘fruit’ and ‘vegetable’ are categories the rest subcategories, i know on this toppic are already a lot of questions which are answered. But my question is:

how can i join “ID” and “parent_id”. currently I am using “xampp” and when i join the values xampp doesn’t allow:

parent_id = 0;

because it is linked with a primary and foreign key to “ID”.

Answer

Use a self join with an appropriate ORDER BY clause:

SELECT
    CASE WHEN c1.parent_id = 0 THEN c1.name ELSE ' - ' || c1.name END AS name
FROM category c1
LEFT JOIN category c2
    ON c1.parent_id = c2.ID
ORDER BY
    COALESCE(c2.ID, c1.ID),
    c1.ID;

Note: The above query might have to be adjusted slightly depending on your actual SQL database.

Advertisement

Answer