SQL query question...

[abkkn]

Hello,

I have the table below:

[ ID ] [ NAME ] [ RELATED_ID ]
[ 1 ] [ Item_1 ] [ 3 ]
[ 2 ] [ Item_2 ] [ 1 ]
[ 3 ] [ Item_3 ] [ 5 ]
[ 4 ] [ Item_4 ] [ 4 ]
[ 5 ] [ Item_5 ] [ 1 ]

How can i query the following output ?

[ ID ] [ NAME ] [ RELATED_NAME ]
[ 1 ] [ Item_1 ] [ Item_3 ]
[ 2 ] [ Item_2 ] [ Item_1 ]
[ 3 ] [ Item_3 ] [ Item_5 ]
[ 4 ] [ Item_4 ] [ Item_4 ]
[ 5 ] [ Item_5 ] [ Item_1 ]

As you can see, i want to display the names for the related items, instead of ids from the same table.

Thanks in advance.

Abkkn.

[womalley]

Originally posted by abkkn
Hello,

I have the table below:

[ ID ] [ NAME ] [ RELATED_ID ]
[ 1 ] [ Item_1 ] [ 3 ]
[ 2 ] [ Item_2 ] [ 1 ]
[ 3 ] [ Item_3 ] [ 5 ]
[ 4 ] [ Item_4 ] [ 4 ]
[ 5 ] [ Item_5 ] [ 1 ]

How can i query the following output ?

[ ID ] [ NAME ] [ RELATED_NAME ]
[ 1 ] [ Item_1 ] [ Item_3 ]
[ 2 ] [ Item_2 ] [ Item_1 ]
[ 3 ] [ Item_3 ] [ Item_5 ]
[ 4 ] [ Item_4 ] [ Item_4 ]
[ 5 ] [ Item_5 ] [ Item_1 ]

As you can see, i want to display the names for the related items, instead of ids from the same table.

Thanks in advance.

Abkkn.

You have to inner join the table to its self. This is a little tricky because you will need to call the table by 2 different names so you dont get any errors. Try something like this.

SELECT A1.ID, A1.Name, MyTable .Related_Name, MyTable .Related_ID FROM MyTable AS A1 INNER JOIN MyTable ON A1.ID = MyTable.Related_ID

this should get you close to what you want.

Will

[abkkn]

thanks, Will

i have tried the following upon your answer, and it did work great... :)

table test00:
[ id ] [ name ] [ rel_id ]
[ 1 ] [ Item1 ] [ 3 ]
[ 2 ] [ Item2 ] [ 1 ]
[ 3 ] [ Item3 ] [ 5 ]
[ 4 ] [ Item4 ] [ 4 ]
[ 5 ] [ Item5 ] [ 1 ]

SELECT test00.id, test00.name, A1.name AS rel_name
FROM test00 AS A1
INNER JOIN test00
ON A1.id = test00.rel_id

and here's the output:
[ id ] [ name ] [ rel_name ]
[ 1 ] [ Item1 ] [ Item3 ]
[ 2 ] [ Item2 ] [ Item1 ]
[ 3 ] [ Item3 ] [ Item5 ]
[ 4 ] [ Item4 ] [ Item4 ]
[ 5 ] [ Item5 ] [ Item1 ]

Thank you very much again...