Query to query itself??

[Mich]

I'm not a programmer, so I'm betting there is an easy solution for this.

I have a table similar to the following:

ID Name RelatedID RelatedName
1 A 1 <TBD>
2 B 3 .
3 C 3 .

The relatedID field provides an alternate name (RelatedName) for a customer. I need to populate the RelatedName field by querying ID and pulling the name field. My results should look similar to this,

ID Name RelatedID RelatedName
1 A 1 A
2 B 3 C
3 C 3 C

I can handle the RelatedName fields for IDs 1 and 3. How do I handle it for ID 2? Make sense?

Thanks in advance.

-If it ain't broke, break it and make it better.

 

[hwkranger]

This is impossible as you will see below..

UPDATE
 T1
SET
 RelatedName = T2.Name
FROM
 Table T1 INNER JOIN Table T2
  ON T1.RelatedID = T2.ID

jk.

Cheers.

 

[Mich]

It's not two tables, it is one, so I need to set RelatedName equal to Name when ID equals RelatedID. However, this doesn't work for ID 2 because the IDs aren't equal.

So when <> I need to query the table for the value of Name on RelatedId and populate RelatedName with it, but I don't know how.

I'm probably just confusing myself.

-If it ain't broke, break it and make it better.

 

[danvlas]

select
A.id as id
,a.name as origname
,b.name as relatedname
From
Tablename a
Inner join tablename b
On a.id=b.relatedid

HTH


Daniel Vlas
Systems Consultant

 

[SQLSister]

Mich you need two tables to do this. This is called a self join. Have you tried the code above?

"NOTHING is more important in a database than integrity." ESquared

 

[Qik3Coder]

[COLOR=blue]Declare[/color] @tmpTable [COLOR=blue]as[/color] [COLOR=blue]Table[/color](
intID [COLOR=blue]int[/color] [COLOR=blue]Identity[/color](1,1),
Txt [COLOR=blue]varchar[/color](15),
RelatedID [COLOR=blue]int[/color])

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @tmpTable
[COLOR=blue]Select[/color][COLOR=red]'First'[/color], NULL
UNION ALL
[COLOR=blue]Select[/color] [COLOR=red]'Second'[/color], NULL
UNION ALL
[COLOR=blue]SELECT[/color] [COLOR=red]'Third'[/color], 1

[COLOR=blue]Select[/color] t1.intID, t1.Txt, [COLOR=#FF00FF]Coalesce[/color](t2.Txt,t1.Txt), *
[COLOR=blue]from[/color] @tmpTable t1
[COLOR=blue]Join[/color] @tmpTable t2 [COLOR=blue]ON[/color]
t2.intID = [COLOR=#FF00FF]Coalesce[/color](t1.RelatedID, t1.intID)

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[hwkranger]

It's not two tables, it is one, so I need to set RelatedName equal to Name when ID equals RelatedID. However, this doesn't work for ID 2 because the IDs aren't equal.

I know it's 1 table. That's why i did a self join. T1 and T2 are alias'

So when <> I need to query the table for the value of Name on RelatedId and populate RelatedName with it, but I don't know how.

My query does this exactly. Replace the words Table with the name of your table.

Mich you need two tables to do this. This is called a self join. Have you tried the code above?

Require once, alias'd twice

Declare @tmpTable as Table(
intID int Identity(1,1),
Txt varchar(15),
RelatedID int)

insert into @tmpTable
Select'First', NULL
UNION ALL
Select 'Second', NULL
UNION ALL
SELECT 'Third', 1

Select t1.intID, t1.Txt, Coalesce(t2.Txt,t1.Txt), *
from @tmpTable t1
Join @tmpTable t2 ON
t2.intID = Coalesce(t1.RelatedID, t1.intID)

I'm not even touching this one.

 

[danvlas]

hwkranger: why update something that is already there?

A simple self join is enough to display everything...


Daniel Vlas
Systems Consultant

 

[Qik3Coder]

Hwk, change the Select to an update, leave the from and the join.

Though why have an id, and a text value for the key is beyond me. You should only need the id.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[hwkranger]

hwkranger: why update something that is already there?

Why not? (i'm sure this will get me into trouble)

Actually, it's because I was lazy. Not a good answer -- but if a = b, and you set a = b again is there real harm? (the real answer is yes, but do you know why?)

Hwk, change the Select to an update, leave the from and the join.

Though why have an id, and a text value for the key is beyond me. You should only need the id.

Because everyone should have an nvarchar(5000) column on every table. It's the fundamental rule of databases. See Boyce-Codd's white paper from 1969 titled, "Put all that $h*t in Duplicate Columns as Text"