CODEWITHSUNDEEP
mysqlselect
Marcin
Marcin

Reputation: 105

Two SELECT's in one query in the same table

I have one table:

id | parent_id | name
1  | NULL      | audi
2  | 1         | a5
3  | 1         | a6
4  | NULL      | opel
5  | 4         | astra
6  | 4         | vectra

I want get name of record and name of record's parent.

One example is to fetch the name for id=5 and its parent name.

id | name  | parent_name
5  | astra | opel

What would be SQL query for this?

My query:

SELECT name, parent_id FROM `cats` WHERE `id` = 5 OR `id` = 
cats.parent_id LIMIT 0 , 30

didn't work.

Upvotes: 3

Views: 6483

Answers (3)

Praneeth Peiris
Praneeth Peiris

Reputation: 2088

This worked fine for me. Please check it out.

select a.id,a.name,b.name as parent from cats a,cats b where b.id=a.parent_id;

You can add any other conditions too. (make sure to use the correct table identifier; a or b)

Upvotes: 0

AllTooSir
AllTooSir

Reputation: 49372

This work's if you have only 1 parent at a time (no recursion):

SELECT a.name name, b.name parent_name
FROM tablexyz a,tablexyz b where
a.id=5 AND a.parent_id=b.id;

Upvotes: 1

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

You can use the below query:

SELECT T1.id, T1.name, T2.name as parentname
FROM TABLE1 T1
    INNER JOIN TABLE1 T2 ON T1.id = T2.parent_id
WHERE T2.ID = 5

SQL FIDDLE

Upvotes: 1

Related Questions