CODEWITHSUNDEEP
mysqlsql
Sterling Duchess
Sterling Duchess

Reputation: 2080

Double select from a same table

I have a table categories that looks like this. My categories can span up to 4 levels deep but I have chosen to always show only one level bellow the selected one.

| id | parent_id | name | ... |
| 1  |    0      |  A   | ... |
| 2  |    1      |  A1  | ... |
| 3  |    2      |  A2  | ... |
| 4  |    1      |  A3  | ... |
| 5  |    0      |  B   | ... |

Basically I'm trying to select parent categories and their first level children. So after select I should get all rows where parent_id = 0 and where parent_id = parent_id from the parents.

I should get:

| id | parent_id | name | ... |
| 1  |     0     |  A   | ... |
| 2  |     1     |  A1  | ... |
| 4  |     1     |  A2  | ... |
| 5  |     0     |  B   | ... |

I tried:

SELECT * AS t1 FROM categories
    WHERE parent_id = 0 AND WHERE parent_id = t1.id;

I'm very new to SQL with code I could do this easy with PHP but I don't want to select everything and then do a complex sort function.

How can I select the table once to select parent_id and then do a second select into this select I just did where parent_id's from categories table equal id's in the select I just did.

Upvotes: 0

Views: 3117

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166376

How about something like

SELECT  *
FROM    categories
WHERE   parent_id = 0 
UNION ALL
SELECT  c.* 
FROM    categories c INNER JOIN
        categories p    ON  c.parent_id = p.id
WHERE   p.parent_id = 0

Upvotes: 2

ghargedeepak
ghargedeepak

Reputation: 125

You can use self join to achieve the same,

SELECT *  FROM categories c1,
               categories c2 
          WHERE c1.parent_id = 0 
          AND c1.parent_id = c2.id

Upvotes: 1

Related Questions