Convert Comma-delimited multiple values

[pl12987]

I am joining two tables in a query, where the value in Table 1 foreign key column "authorsid" corresponds to the Table 2 primary key "authorsid". My problem is that some rows in the foreign key table have multiple comma-delimited values ("46, 13&quot and my SELECT statement fails with the message : Syntax error converting nvarchar value "46, 13" to datatype int.

Of course what I want is for the query to LOOP THRU the list of each value in turn and use the value as the PK lookup in the other table.

How do I get it to do that?

 

[rac2]

Hello,

I think you must mean that you would like to use table2.authorsid as a foreign key for table1, not that it is a foreign key. I say this because the database engine would prevent any values from being entered into table1.authorsid that did not match a value in table2.authorsid if table1 was created with a foreign key constraint on authorsid.

So maybe you will need to clean up the data in the table that has multiple values in authorsid. For that I would write a procedure to break out those values and create a row for each one in a new table.

Hope this clarifies the situation.
Richard

 

[tlbroadbent]

I thoroughly agree with Richard. The database design is poor. It would be much easier to work with if the database were properly designed and proper primary/foreign key relationships established.

However, you can accomplish what you want given the current design. This solution depends on the data being consistently entered in table2 as comma or space separated numbers.
[tt]
Select *
From table1 t1, table2 t2
Where ltrim(str(t2.authorsid))=t1.authorsid
Or charindex(','+ltrim(str(t2.authorsid))+',', t1.authorsid)>0
Or charindex(' '+ltrim(str(t2.authorsid))+',', t1.authorsid)>0
Or charindex(','+ltrim(str(t2.authorsid))+' ', t1.authorsid)>0
Or charindex(' '+ltrim(str(t2.authorsid))+' ', t1.authorsid)>0
Or ltrim(str(t2.authorsid))=
case charindex(',',t1.authorsid)
When 0 Then ''
Else left(t1.authorsid,charindex(',',t1.authorsid)-1) End
Or ltrim(str(t2.authorsid))=
case charindex(' ',t1.authorsid)
When 0 Then ''
Else left(t1.authorsid,charindex(' ',t1.authorsid)-1) End
Or ltrim(str(t2.authorsid))=
case charindex(',',t1.authorsid)
When 0 Then ''
Else right(t1.authorsid,charindex(',',reverse(t1.authorsid))-1) End
Or ltrim(str(t2.authorsid))=
case charindex(' ',t1.authorsid)
When 0 Then ''
Else right(t1.authorsid,charindex(' ',reverse(t1.authorsid))-1) End
[/tt]
Besides being ugly, this query may run very slowly for large tables. It cannot use indexes and uses several intrinsic functions repeatedly.

Let me know how it goes and if you have any questions about the query. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[pl12987]

tlbroadbent, thank you for the code. I appreciate the solution. However, I agree that if this kind of brute force is what is needed, I'm doing something wrong in the first place. (Yes, I am a novice at db design.) Also, I wasn't clear in my initial post. There actually is no formal foreign key constraint at all, it is just a one-to-many relationship where I need the freedom to add and subtract at will (both columns are updated at once, though). The problem is that one table is authors and the other is articles, for a publication. One author may have written several articles. How am I supposed to have a table of authors if I cannot associate more than one article value for each of them? Or multiple authors for a single article?

 

[pl12987]

Never mind, I finally got it. I have to break out the info into new tables...doh! In fact, I need two, one for author's articles and one for article's authors. Forgot the rule: when in doubt, break it out.