| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-10 : 19:29:57
|
| Ken writes "Hi there,My problem is a problem that shouldn't exist.I have two tables, one stores the data with an id (nvarchar) field delimited by commas "," such as:[Table A].IDField10050,100,150100,150etc.The other stores the data with an id (int) such as:[Table B].IDField50100150etc.I need to JOIN these two tables together somehow using an IN clause(LIKE clause takes too long to run) as in:[Table B].IDField IN([Table A].IDField)This doesn't work ...I even went as far as to place '(commas)' to distinguish the delimited values from eachother to delimit everything such as:"'"+Convert(varchar,[Table B].IDField)+"'" IN("'"+REPLACE([Table A].IDField,",","','")+"'")eg. '100' IN ('50','100','150')Hoping that it could return the '100' when it is delimited with other values, but still it cannot ... it can only return the '100' in [Table A] that is stored individually.Logically, I don't understand why this doesn't work.Sincerely,Ken" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-10 : 20:34:18
|
| Couple of things:-Using CONVERT() to make a number a varchar is inconsistent, especially without specifying a length (varchar(10), for example). Use the STR() function instead as it allows more precision in the string value-Since the IDField in TableA is nvarchar, you need to convert the value to nvarchar for it to work (if this can work at all, because...)-Storing multiple CSV numbers in a character column not only violates good database theory, the design leads to problems like the one you're having now. It may not be possible to get this type of join to work correctly. |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-10 : 20:36:49
|
quote:
My problem is a problem that shouldn't exist.I have two tables, one stores the data with an id (nvarchar) field delimited by commas "," such as:
Damn right it shouldn't exist. You have broken Rule #1 of database design.Is it too late to go fix your database ? There is a way around this, but it is WAY better to fix your data model.Put it this way, if I were building you a house. Would you like it, if I use some cardboard and sticky tape in the foundation, then slapped a quick fix up near the roof to hold your house up ? NO.Get the foundations right, and the whole thing will be better.DamianP.S.Byrmol, I KNOW you want to jump in here  |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-02-10 : 20:46:28
|
quote:
Damn right it shouldn't exist. You have broken Rule #1 of database design.Is it too late to go fix your database ? There is a way around this, but it is WAY better to fix your data model.Put it this way, if I were building you a house. Would you like it, if I use some cardboard and sticky tape in the foundation, then slapped a quick fix up near the roof to hold your house up ? NO.Get the foundations right, and the whole thing will be better.DamianP.S.Byrmol, I KNOW you want to jump in here
MUST... STOP... SELF... FROM... KILLING.....(Collapses from self restraint)DavidMTomorrow is the same day as Today was the day before. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-10 : 20:47:53
|
quote:
(Collapses from self restraint)
Isn't that what happened to Michael Hutchence ?Damian |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-10 : 21:13:50
|
| Bad database design kills Michael Hutchence - well lets face it - it's probably more believable than what I read in the papers....--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-02-10 : 21:18:23
|
quote:
Bad database design kills Michael Hutchence - well lets face it - it's probably more believable than what I read in the papers....
I have it on pretty good authority that it was a piece of confectionary....DavidMTomorrow is the same day as Today was the day before. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-10 : 21:23:09
|
<edit>BAD BAD BAD Merkin</edit> DamianEdited by - Merkin on 02/10/2002 21:24:48 |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-10 : 21:27:19
|
quote:
an all day sucker
I believe it only lasted until abou 4am...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-10 : 21:30:11
|
He was hanging around for a while longer though  Damian |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-10 : 21:36:04
|
| I just have to reply - only I can't think of anything funnier than that--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-02-11 : 05:38:09
|
| It's strange that so many people make this category error. They seem unable to tell the difference between a syntactic construct for a set of literal values (which happens to use commas) and a CSV.Nonetheless, if you don't fix the design, you will have to use LIKE (or CHARINDEX):',' + [Table A].IDField + ',' LIKE '%,' + CONVERT(varchar, [Table B].IDField) + ',%'orCHARINDEX(',' + CONVERT(varchar, [Table B].IDField) + ',', ',' + [Table A].IDField + ',') > 0BTW, As Dead Australian Popstars go, I prefer David McComb.Edited by - Arnold Fribble on 02/11/2002 05:40:03 |
|
|
|
|
IN clause (text field)