Find data in comma-delimited lists 1

[katehol]

I need to see if a certain integer appears in a column that contains multiple comma-delimited lists.

eg SELECT * FROM TableA
WHERE TableA.id IN (SELECT list FROM TableB)

...where 'TableB.list' is a comma-delimited list, and there are multiple rows in this TableB table, therefore meaning that there are multiple comma-delimited lists to search for the TableA.id.

Any help would be much appreciated.

Thanks

 

[SQLDenis]

That is not what I said, I said either they had to fix their stuff or we wouldn't use them
And if I came across a DB like that I would redesign it ASAP because I know if would haunt me sooner or later

it is your choice live with a kludge and wonder when it will slow down that you have to fix it anyway or fix it now. For me the choice is pretty easy since the change is not that great

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

And after 50 years, we still don't have time to do it right the first time, but we'll find time to fix it...later.

Don't live with broken windows. Fix bad designs, wrong decisions, and poor code when you see them.

http://www.pragprog.com/the-pragmatic-programmer/extracts/tips

< M!ke >
[small]Where are we going and why am I in this handbasket?[/small]

 

[Crowley16]

Denis, how can you say that the change is not that great?

you've not looked at the system, information hasn't been provided on all the linked/related functionality linked to these particular tables, there's just no way you can know something like that...

What if that table is an integral part of a huge front end application, and any change to that table will require trauling through 10's of thousands of lines of code across 100's of source files/stored procedures/views...?

You think you can do that in 2 hours?

Frankly I wouldn't be surprised if there were no database objects at all in the application and all queries were fired directly at the database from the front end, and there are 100's of different locations where the queries can come from.

Don't get me wrong, I totally agree the system should be normalised asap, but you cannot just say it isn't a lot of work without knowing anything about the system.

--------------------
Procrastinate Now!

 

[SQLDenis]

>>Denis, how can you say that the change is not that great?

Here is what I said: "Unless you have inline SQL all over the place it would be faster to redesign this thing now once and for all."


What I meant was that if you use stored procs the change would not be great, this is also the reason I mentioned Coupling, Cohesion and Encapsulation

Tightly coupled systems are a nightmare to refactor/change!

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Crowley16]

how likely is it that the person who developed this used stored procedures/views/udfs for all database access, and they follow any sort of style?

The poster said it was to be redisigned, so I don't really see the need for:

2 hours maybe. It has been 2 hours since you posted the question, get the point.

Stop pressing the F5/CTRL + R keys in hope for an answer and redesign now

--------------------
Procrastinate Now!

 

[damber]

I like your sig Crowley16.

=======================================
LessThanDot - The IT Community of the 21st Century

A smile is worth a thousand kind words. So smile, it's easy!

 

[SQLDenis]

I guess that was a bit harsh :-(

However I stand by my point that this 'solution' might break before they plan on fixing it and then what? Suddenly there is time because someone is working in firehose mode? Fix it now and you can be sure it won't creep up on you later

Fixes like this I have seen done by consultants who then move on. 2 years later it breaks down and there is nobody who knows how to fix this stuff



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Crowley16]

My sig is a quote from Ellen, I think. It's extremely appropriate since I'm extremely lazy.

--------------------
Procrastinate Now!