CODEWITHSUNDEEP
sqlms-accesslookup
chrisSpaceman
chrisSpaceman

Reputation: 249

Value in one field as lookup from same table

I'm certain this is very easy, but I am very poor at database stuff...

I have the following table in access 2003:

title        |     id
/root        |      1
/root/x      |      2
/root/x/y    |      3
/root/x/y/z  |      4
/root/x/a    |      5
/root/x/a/b  |      6

i.e. a bunch of nodes and id numbers - you can see that /root/x is the parent of /root/x/y. I'd like to create another table which has a list of all the nodes, along with the id's of their parents. i.e:

id  | parent id
1   |   -
2   |   1
3   |   2
4   |   3
5   |   2
6   |   5

The follwing will give me the id and the value of the parent: 

select id, left(c.title, instrrev(c.title, "/")-1)  as parentValue from nodeIDs

yields

id |  parentNode
1  |
2  |  /root 
3  |  /root/x 
4  |  /root/x/y
5  |  /root/x
6  |  /root/x/a

What is the extra step needed to return the id's of those parent nodes, rather than their values, i.e, return '1' instead of '/root' in that last table?

Many thanks

Upvotes: 4

Views: 3877

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Something like this perhaps:

select c.id, 
left(c.title, instrrev(c.title, "/")-1)  as parentValue
, p.id as parentID
from nodeIDs c
left join
nodeIDs p
on left(c.title, instrrev(c.title, "/")-1) = p.title

Upvotes: 2

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

Something along these lines, I think.

select t1.id, 
       left(t1.title, instrrev(t1.title, "/")-1)  as parentNode,
       t2.id as parentID
from nodeIDs t1
inner join nodeIDs t2 on (left(t1.title, instrrev(t1.title, "/")-1)) = t2.title

I don't have any easy way to test this. But the basic idea is that, having derived the title of the parent node, you can do an inner join on it to get the associated id number.

Upvotes: 0

Related Questions