CODEWITHSUNDEEP
sql
rookie coder
rookie coder

Reputation: 141

SQL - query and replace column value with another value extracted from the same table

I have a table with the following columns:

ID  |  NAME  | ID_PREVIOUS  | ID_FOLLOWING |   RANDOM_USELESS_DATA
----+--------+--------------+--------------+---------------------
1   | AB2-F  |     NULL     |     2        |      bla bla bla
2   | PP-01  |      1       |     3        |      ffwfwqafrwf
3   | 10J6U  |      2       |     7        |      ihjfoiwhfo
7   | F-1-R  |      3       |   NULL       |      fdojshflos

This table represent a kind of graph. Every line is a node with its id, its name, the id of the previous and following node and some other data that is not useful for the question. Notice that the adiacent nodes could be null, meaning that there is only one connection for the specific node.

What I cannot do is to make a query that substitutes all the IDs with the corresponding node name. I need to represent the "graph" with the names but i don't know how to do. My ideal result would be:

NAME  |  NAME_PREVIOUS |   NAME_FOLLOWING
------+----------------+------------------
AB2-F |    NULL        |  PP-01
PP-01 |    AB2-F       |  10J6U
10J6U |    PP-01       |  F-1-R
F-1-R |    10J6U       |  NULL

Thanks for your help.

Upvotes: 0

Views: 5433

Answers (4)

user77318
user77318

Reputation: 80

SELECT  a.NAME as NAME, b.NAME as NAME_PREVIOUS, c.NAME as NAME_FOLLOWING
FROM GRAPH_TABLE a LEFT JOIN GRAPH_TABLE b on a.ID_PREVIOUS=b.ID
LEFT JOIN GRAPH_TABLE c on a.ID_FOLLOWING=c.ID.

You can check the example here : http://sqlfiddle.com/#!2/c73c4/3

Upvotes: 1

user330315
user330315

Reputation:

If you only have a single level of "previous/following" you can get away with a self-join

select nd.name, 
       pr.name as name_previous,
       nx.name as name_following
from nodes nd 
  left join nodes pr on nd.id_previous = pr.id
  left join nodes nx on nd.id_following = nx.id;

SQLFiddle: http://sqlfiddle.com/#!15/21b46/2

If you have multiple levels you need a recursive query (which is not supported by MS-Access or MySQL)

Upvotes: 1

sarh
sarh

Reputation: 6627

SELECT T.NAME, 
TP.NAME AS NAME_PREVIOUS,
TF.NAME AS NAME_FOLLOWING
FROM YOUR_TABLE AS T
LEFT JOIN YOUR_TABLE AS TP TP.ID = T.ID_PREVIOUS 
LEFT JOIN YOUR_TABLE AS TF TF.ID = T.ID_FOLLOWING

That's general SQL that should work for the most DBMS I suppose, however as I know Access query builder for some reasons also adds bracket in FROM clause like this:

SELECT T.NAME, 
TP.NAME AS NAME_PREVIOUS,
TF.NAME AS NAME_FOLLOWING
FROM (YOUR_TABLE AS T
LEFT JOIN YOUR_TABLE AS TP TP.ID = T.ID_PREVIOUS) 
LEFT JOIN YOUR_TABLE AS TF TF.ID = T.ID_FOLLOWING

Upvotes: 1

Nikhil Talreja
Nikhil Talreja

Reputation: 2774

UPDATE table T
SET T.NAME_PREVIOUS = (SELECT NAME FROM table WHERE ID = T.ID_PREVIOUS),
T.NAME_FOLLOWING = (SELECT NAME FROM table WHERE ID = T.ID_FOLLOWING);

Upvotes: 0

Related Questions