May 16, 2007 at 9:54 am
I need to link a group of IDs separated by commas to another table. Here are some details:
Table 1: dbo.Registrations(regKy int, regName varchar(100), regInterests varchar(50) );
Table 2: dbo.Interests (FK_intersts int, intName varchar (50) );
Run SQL 'Select * From dbo.Registrations' will generate a list of records like this one: 1, 'someones name', '1,3,5'
Run SQL 'Select * From dbo.Interests' will generate a list of interests like this one: 1, 'travel'
How do I write a query to link two tables where each ID in dbo.Registrations.regInterests will be linked dbo.Interests.FK_interst so I can display dbo.InterestsintName in the results?
May 16, 2007 at 11:47 am
First hire someone who knows how to design a database in 3rd normal form. I would hate to see what the rest of your database looks like.
If you must have it the way it is (I really recommend changing this design!) then something like this:
NOTE: I will repost the fnListSplit function originally posted by Peter Larson, see http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=283446&p=1 at the very end of this post for convience. You will need to create that function first then run my scripts here.
set nocount on
if object_id('registrations','u') is not null drop table registrations
go
if object_id('Interests','u') is not null drop table Interests
go
create table registrations (regky int, regname varchar(100), reginterests varchar(50))
go
create table Interests (fk_interests int, intname varchar(50))
go
insert into Interests values (1,'one')
insert into Interests values (2,'two')
insert into Interests values (3,'three')
insert into Interests values (4,'four')
insert into Interests values (5,'five')
insert into Interests values (6,'six')
go
insert into registrations values (1,'test1','1,2,3')
insert into registrations values (2,'test1','3,4,5')
insert into registrations values (3,'test1','1,3,6')
insert into registrations values (4,'test1','5,3')
go
IF OBJECT_ID ('dbo.normalizeit_') IS NOT NULL
DROP FUNCTION dbo.normalizeit_
GO
CREATE FUNCTION dbo.normalizeit_ () RETURNS @t1_ table (regky int, fk_interests int)
AS
BEGIN
declare @regKy_ int
declare @regInterest_ varchar(50)
declare cur1_ cursor for
select regky, reginterests from registrations
open cur1_
fetch next from Cur1_ INTO @regKy_, @regInterest_
while @@fetch_status = 0
begin
insert into @t1_ (regky, fk_interests)
select @regky_,f.x from dbo.fnListSplit(@regInterest_, ',') f
fetch next from Cur1_ INTO @regKy_, @regInterest_
end
return
END
go
--select * from dbo.fnListSplit('1,2,3,4', ',') f
--select * from dbo.normalizeit_()
select registrations.regky,registrations.regname,Interests.intname
--select *
from registrations join dbo.normalizeit_() f_ on (registrations.regky = f_.regky) join Interests on (f_.fk_interests = Interests.fk_interests)
----------------------------------------------------------------------------------------------
----------- The following function originally posted by Peter Larson ------------------------
----------------------------------------------------------------------------------------------
IF OBJECT_ID ('dbo.fnListSplit') IS NOT NULL
DROP FUNCTION dbo.fnListSplit
GO
CREATE FUNCTION dbo.fnListSplit
(
@List VARCHAR(8000),
@Delimiter VARCHAR(2)
)
RETURNS @Resultset TABLE (i SMALLINT IDENTITY(0, 1), x VARCHAR(8000))
AS
BEGIN
INSERT @Resultset
(
x
)
SELECT SUBSTRING(@Delimiter + @List + @Delimiter, w.i + 1, CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.i + 1) - w.i - 1)
FROM (
SELECT v0.n + v1.n + v2.n + v3.n i
FROM (
SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
) v0,
(
SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL SELECT 64 UNION ALL SELECT 80 UNION ALL SELECT 96 UNION ALL SELECT 112 UNION SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
) v1,
(
SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768 UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792 UNION SELECT 2048 UNION ALL SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
) v2,
(
SELECT 0 n UNION ALL SELECT 4096
) v3
) w
WHERE w.i = CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.i) AND w.i < LEN(@Delimiter + @List)
ORDER BY w.i
RETURN
END
May 16, 2007 at 10:15 pm
Unless you need absolutely awesome performance on a huge table, this'll do nicely with pretty good performance...
SELECT i.IntName,r.RegName,r.RegInterests
FROM Registrations r,
Interests i
WHERE ','+r.RegInterests+',' LIKE '%,'+CAST(i.fk_Interests AS VARCHAR(10))+',%'
Although a bit rough in his statement, JLK is absolutely correct... CSV columns are one of the worst things that can happen to a database.
If you decide to normalize the whole table, post back... I've got some pretty good code to do that at a pretty good clip.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply