Linking 2 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Linking 2 tables

Author

Topic

dkloehr
Starting Member

1 Post

Posted - 2008-09-05 : 21:57:44

My problem is this:

In table 1 I have a field which contains a field with a name (ie, John, Ted, Sam) and a text field which contains several numbers, each seperated by a comma and quotes (i.e. '1234', '2345', etc)

So the structure of table 1 is something like this:

FIELD1 FIELD2
John '1234', '2345'
Ted '2346', '3456'
Sam '3333', '4444'

In table 2 I have several records, each containing one number and one city.

Table 2 looks like this:

FIELD1 FIELD2
1234 Denver
2345 New York
2346 Los Angeles

I want to know how to link the tables to get the following:

FEILD1 FIELD2 FIELD3
John 1234 Denver
John 2345 New York
Ted 2346 Los Angeles
etc.

Any help is greatly appreciated. Using SQL 2005 on Windows XP.

Dennis

raky
Aged Yak Warrior

767 Posts

Posted - 2008-09-06 : 01:43:33

hi try this

declare @tab1 table ( field1 varchar(64), field2 varchar(max))
insert into @tab1
select 'John' ,'1234,2345' union all
select 'Ted', '2346,3456' union all
select 'Sam', '3333,4444'

declare @temp table ( name varchar(64), field varchar(64))

insert into @temp
SELECT t1.field1,
SUBSTRING(t1.field2, v.Number - 1, COALESCE(NULLIF(CHARINDEX(',', t1.field2, v.Number), 0), LEN(t1.field2) + 1) - v.Number + 1) AS field2

FROM @tab1 AS t1

INNER JOIN master..spt_values AS v ON v.Type = 'p'

WHERE SUBSTRING(',_' + t1.field2, v.Number, 1) = ','

declare @tab2 table ( field1 varchar(30), field2 varchar(64))

insert into @tab2
select '1234','Denver' union all
select '2345','New York' union all
select '2346','Los Angeles'

select t1.name, t2.field1, t2.field2
from @temp t1
left join @tab2 t2 on t2.field1 = t1.field

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-06 : 23:32:48

[code]SELECT t1.Name,t1.Number,t2.FIELD2 AS FIELD3
FROM
(SELECT PARSENAME(REPLACE(FIELD1,' ','.'),1) AS Number,PARSENAME(REPLACE(FIELD1,' ','.'),2) AS Name
FROM Table1
UNION ALL
SELECT FIELD2,PARSENAME(REPLACE(FIELD1,' ','.'),2) AS Name
FROM Table1)t1
INNER JOIN Table2 t2
ON t2.FIELD1=t1.Number
ORDER BY t1.Name,t1.Number
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources