What will be the SQL query for this problem?

Question

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

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

for example:
"article1" has a parent "Sub Category1" and its parent is "Primary category". Thus, the result should have rows 1,2,4 from the table.

Is it possible to write a single SQL query for this? What will be the query that can retrieve data in accordance with the above condition?

Please help me..!!

thank..

Answer 1

, : MySQL.

, . lft () rgt () . lft rgt

Root lft 1. lft , , , node (leaf node) rgt lft +1. lft rgt +1 .

, rgt rgt +1.

, .

, ,

:

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;

DELYTING :

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;

:

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;

, .

Answer 2

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 response syntax. Alex Aza & rsquo; s, but in MySQL I don’t think you can do this in a single 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;

Required RECURSIVE.

Answer 4

if you are using sqlserver 2008 try the MSDN hierarchy