Posted by hharry on 09/17/05 05:06

Hello All,

I have the following table:

CREATE TABLE [dbo].[TBL_NAME] (
[NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

With values:

insert into tbl_name
values('DAN', 'DANIEL')
insert into tbl_name
values('DANNY', 'DANIEL')
insert into tbl_name
values('DANYY', 'DANIEL')

Question is:

I need want to construct a query which returns all names for a standard
name plus the standard name itself.
e.g.
if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'

i have the following sql:

declare @name varchar(50)
select @name = 'DANIEL'
select standard_name from tbl_name where name = @name
union
select name from tbl_name where standard_name = (select standard_name
from tbl_name where name = @name)
union
select name from tbl_name where standard_name = @name
union
select standard_name from tbl_name where standard_name = @name

----

declare @name varchar(50)
select @name = 'DANNY'
select standard_name from tbl_name where name = @name
union
select name from tbl_name where standard_name = (select standard_name
from tbl_name where name = @name)
union
select name from tbl_name where standard_name = @name
union
select standard_name from tbl_name where standard_name = @name

----

Both appear to work fine..can anyone see a fault or suggest a cleaner
way to achieve the above ?

Suggestions/pointers appreciated
Thanks in advance

• Next in forum: Re: Query Syntax help
• Prev in forum: Re: Removing white spaces in a varchar column
• Thread view: Query Syntax help

[Reply to this message]