CODEWITHSUNDEEP
mysql
Daniel Williams
Daniel Williams

Reputation: 2317

Joining MySQL results from the same table

I'm proficient with joining tables in mySQL, but I'm having trouble with joining results from the SAME table. I'm creating a folder structure in PHP where a folder has an ID, a parent ID, a random-string ID, and a label.

My DB looks like:

| id | parent_id | uniq   | label
---------------------------------
| 1  | 0         | w2d4f6 | dir 1
| 2  | 1         | h9k3h7 | dir 2

The front end uses the uniq var to identify a folder. So In the DB you can see that if I am opening the folder dir 1, the folder dir 2 will be inside it, since dir 2 has dir 1's ID as its parent.

Still with me?

|- dir 1
|  + dir 2

The folder dir 1 is identified by its uniq string, so w2d4f6. So what I'm wanting to do is:

  1. Get the parent ID of the record that has uniq='w2d4f6'
  2. The parent ID is 1
  3. Look for records where parent_id=1

I know this is totally wrong, and I think I should be using JOIN but I tried the following without success.

SELECT folders.label,folders.parent_id FROM folders WHERE folders.uniq='w2d4f6' AND folders.id=folders.parent_id

Upvotes: 0

Views: 29

Answers (2)

Thijn
Thijn

Reputation: 165

This should work if you already have the parent data and just want to request the child data by the uniq value of the parent:

SELECT label, parent_id FROM folders WHERE parent_id IN (SELECT id FROM folders WHERE uniq='w2d4f6')

Upvotes: 1

jspcal
jspcal

Reputation: 51934

To get the children of a folder:

select b.label, b.parent_id
from folders a, folders b
where a.uniq = 'w2d4f6' AND b.parent_id = a.id

Upvotes: 2

Related Questions