What will be the SQL query for this problem?

Question

I want to fetch some data from the following database table 'article'.

i want to fetch an article information whose category_id is 4. But the result should contain all the records of its parent categories.

for example :
'article1' has parent 'Sub Category1' and its parent is 'Main Category'. So the result should have the rows 1,2,4 from the table.

Is it possible to write one SQL query for this? what will be the query which can fetch data according to above condition?

Please guide me..!!

thanks..

Answer 1

Please refer to this article: Managing Hierarchical Data in MySQL.

Actually, they have purposed model to get Hierarchical data without recursion. In that they use lft(left) and rgt(right) this to extra column for storing structural information of table. lft and rgt are set as follows

Root lft is 1. then its first child lft is next number then its child in next number so on till there are no child then for that node (leaf node) rgt will be its lft +1. And we will set siblings lft as rgt +1 and follow same rules for it also.

And if all child's numbering is done its will set parents rgt to last child's rgt +1.

I haven't explain it neatly but on link with image it is easier to understand.

So after this you can easily explore nested structure using following queries

For getting all parent's id in order:

SELECT parent.category_id
FROM article AS node,
article AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_id = $category_id
ORDER BY parent.lft;

For DELETING any row :

LOCK TABLE article WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
  FROM article WHERE category_id = 'row_id';
DELETE FROM article WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE article SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE article SET lft = lft - @myWidth WHERE lft > @myRight;
UNLOCK TABLES;

For inserting any row:

LOCK TABLE article WRITE;
SELECT @myLeft := lft FROM article  WHERE category_id = 'parent_id';
UPDATE article SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE article SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO article(title, lft, rgt) VALUES('title', @myLeft + 1, @myLeft + 2);
UNLOCK TABLES;

This is somewhat complex, but after creating stored procedure, it will not be difficult to use.

Answer 2

In MS SQL 2005/2008:

with cte(category_id, parent_id, title)
as
(
    select category_id, parent_id, title
    from Category
    where category_id = 4
    union all
    select cat.category_id, cat.parent_id, cat.title
    from Category cat
        join cte on
            cat.category_id = cte.parent_id
)
select *
from cte    

Answer 3

This is known in SQL as a RECURSIVE query. Oracle and Postgresql use a different syntax from Alex Aza’s answer, and in MySQL, I don’t think you can do it in one query.

WITH RECURSIVE t(category_id, parent_id, title) AS
(SELECT category_id, parent_id, title
    from Category
    where category_id = 4)
 UNION ALL
 SELECT c.category_id, c.parent_id, c.title
 FROM Category c, t WHERE t.parent_id=c.category_id  /* ends when parent_id==-1 */
)
SELECT * FROM t;

The RECURSIVE is required.