complicated select

through no fault of my own, there exists a table with varchar(50)
column, the values of which are comma separated integers. something
like so:
create table foo (
fookey int primary key not null,
foointegerlist1 varchar(50) null,
foointegerlist2 varchar(50) null,
fooprice money null,
foosize int null
)
insert into foo values (1, ',2,3', '1', 10, 100)
insert into foo values (2, '3', '4', null, 100)
insert into foo values (3, ',1,11', ',1,5,6', 10, 100)
insert into foo values (4, ',3,5', ',11', 10, null)
(yes, the leading comma will randomly appear)
unpleasant? yes. scheduled to be redesigned? yes. but for now, this is
what i have to contend with.
the problem i'm having is that there is another table with a very
similar column, like so:
create table foomatch (
foomatchkey int primary key not null,
foomatchintegerlist1 varchar(50) null
foomatchintegerlist2 varchar(50) null,
foomatchprice money null,
foomatchsize int null
)
insert into foomatch values (1, ',2', ',4,5,6', 10, 100)
insert into foomatch values (2, ',1,3,6', ',1', null, null)
the goal of the select statement is to capture all of the rows in the
table foo that "match" the values of a given single row in the foomatch
table. the rules for matching are as follows:
foomatch.foomatchprice = foo.fooprice AND
foomatch.foomatchsize = foo.foosize AND
(this is where it gets dicey for me)
at least ONE of the integer values in foomatch.foomatchintegerlist1
must occur in the foo.foointegerlist1 column AND
at least ONE of the integer values in foomatch.foomatchintegerlist2
must occur in the foo.foointegerlist1 column
where foomatch.foomatchkey = 1 (for example)
i have a user defined function that can turn a comma separated string
value into a single column table. i mention this in case building
tables of the values will make this easier / possible (perhaps with
relational math?)
thanks in advance for any help, and just let me know if you need
clarification,
jasonhi jason,
its quite a long story
use string manipulations to meet the desired solution.
we have several string functions that you can use
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"jason" wrote:

> through no fault of my own, there exists a table with varchar(50)
> column, the values of which are comma separated integers. something
> like so:
> create table foo (
> fookey int primary key not null,
> foointegerlist1 varchar(50) null,
> foointegerlist2 varchar(50) null,
> fooprice money null,
> foosize int null
> )
> insert into foo values (1, ',2,3', '1', 10, 100)
> insert into foo values (2, '3', '4', null, 100)
> insert into foo values (3, ',1,11', ',1,5,6', 10, 100)
> insert into foo values (4, ',3,5', ',11', 10, null)
> (yes, the leading comma will randomly appear)
> unpleasant? yes. scheduled to be redesigned? yes. but for now, this is
> what i have to contend with.
> the problem i'm having is that there is another table with a very
> similar column, like so:
> create table foomatch (
> foomatchkey int primary key not null,
> foomatchintegerlist1 varchar(50) null
> foomatchintegerlist2 varchar(50) null,
> foomatchprice money null,
> foomatchsize int null
> )
> insert into foomatch values (1, ',2', ',4,5,6', 10, 100)
> insert into foomatch values (2, ',1,3,6', ',1', null, null)
> the goal of the select statement is to capture all of the rows in the
> table foo that "match" the values of a given single row in the foomatch
> table. the rules for matching are as follows:
> foomatch.foomatchprice = foo.fooprice AND
> foomatch.foomatchsize = foo.foosize AND
> (this is where it gets dicey for me)
> at least ONE of the integer values in foomatch.foomatchintegerlist1
> must occur in the foo.foointegerlist1 column AND
> at least ONE of the integer values in foomatch.foomatchintegerlist2
> must occur in the foo.foointegerlist1 column
> where foomatch.foomatchkey = 1 (for example)
> i have a user defined function that can turn a comma separated string
> value into a single column table. i mention this in case building
> tables of the values will make this easier / possible (perhaps with
> relational math?)
> thanks in advance for any help, and just let me know if you need
> clarification,
> jason
>|||thanks for the reply Jose.
i spent some time looking at string manipulation approaches, i'm not
sure string manipulation will do the trick here. even if we boiled it
down to a single comparison, how would you use string manipulation to
answer the question are any of the comma separated elements in ',1,2,3'
also in the comma separated list ',3,4,5'? i've spent some time going
down this path, and it didn't yield anything close to sane results.
though if you can post something a little more specific, i'd be happy
to entertain the notion.
and yes, my explanations are rarely brief. this isn't even close to one
of my longer ones :)|||If you use tables or table valued user defined functions for the integer
lists it is reasonably straightforward:
Assuming there are tables/udfs foolist1 (fookey, foointeger1) ,
foomatchlist1 (foomatchkey, foomatchinteger1) and foomatchlist2
(foomatchkey, foomatchinteger2)
SELECT f.<column list>
FROM foo f
INNER JOIN foomatch fm
ON fm.foomatchprice = f.fooprice
AND fm.foomatchsize = f.foosize
WHERE EXISTS
(
SELECT NULL FROM foolist1 fl1
INNER JOIN foomatchlist1 fml1
ON fl1.foointeger1 = fml1.foomatchinteger1
WHERE fl1.fookey = f.fookey AND fml1.foomatchkey = fm.foomatchkey
)
AND EXISTS
(
SELECT NULL FROM foolist1 fl1
INNER JOIN foomatchlist2 fml2
ON fl1.foointeger1 = fml2.foomatchinteger2
WHERE fl1.fookey = f.fookey AND fml2.foomatchkey = fm.foomatchkey
)
Jacco Schalkwijk
SQL Server MVP
"jason" <iaesun@.yahoo.com> wrote in message
news:1128432811.427135.37460@.g44g2000cwa.googlegroups.com...
> through no fault of my own, there exists a table with varchar(50)
> column, the values of which are comma separated integers. something
> like so:
> create table foo (
> fookey int primary key not null,
> foointegerlist1 varchar(50) null,
> foointegerlist2 varchar(50) null,
> fooprice money null,
> foosize int null
> )
> insert into foo values (1, ',2,3', '1', 10, 100)
> insert into foo values (2, '3', '4', null, 100)
> insert into foo values (3, ',1,11', ',1,5,6', 10, 100)
> insert into foo values (4, ',3,5', ',11', 10, null)
> (yes, the leading comma will randomly appear)
> unpleasant? yes. scheduled to be redesigned? yes. but for now, this is
> what i have to contend with.
> the problem i'm having is that there is another table with a very
> similar column, like so:
> create table foomatch (
> foomatchkey int primary key not null,
> foomatchintegerlist1 varchar(50) null
> foomatchintegerlist2 varchar(50) null,
> foomatchprice money null,
> foomatchsize int null
> )
> insert into foomatch values (1, ',2', ',4,5,6', 10, 100)
> insert into foomatch values (2, ',1,3,6', ',1', null, null)
> the goal of the select statement is to capture all of the rows in the
> table foo that "match" the values of a given single row in the foomatch
> table. the rules for matching are as follows:
> foomatch.foomatchprice = foo.fooprice AND
> foomatch.foomatchsize = foo.foosize AND
> (this is where it gets dicey for me)
> at least ONE of the integer values in foomatch.foomatchintegerlist1
> must occur in the foo.foointegerlist1 column AND
> at least ONE of the integer values in foomatch.foomatchintegerlist2
> must occur in the foo.foointegerlist1 column
> where foomatch.foomatchkey = 1 (for example)
> i have a user defined function that can turn a comma separated string
> value into a single column table. i mention this in case building
> tables of the values will make this easier / possible (perhaps with
> relational math?)
> thanks in advance for any help, and just let me know if you need
> clarification,
> jason
>|||ahh! i think i see the solution here. yes, i think i can modify my
udf's slightly to make this work. i will give that a shot.
thanks very much
jason

Compatibility Mode 70 databases in SP2

Hi
I would not say that this would be deemed a major issue as it is simple to
write your own maintenance procedures, and there are examples if you search
the web.
It is also recommended that compatibility mode is mainly designed as a
transient part of an upgrade and should not normally be something to be
relied upon long term, unless there is no way you can upgrade change the
database. If the latter is the case then your buisiness may be at risk if you
are relying on such software.
John
"Axel Bender" wrote:

> Hi,
> Does anybody know if the creation of maintenance plans for Compatibility
> Mode 70 databases will be possible with SQL Server 2005 SP2?
> According to what I've heard so far, there is no chance to do that in
> SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
> the wizard). I consider this a severe shortcoming in the product, which
> should be tackled by MS.
> Any opinions/workarounds?
>
Hi Axel
I suggest that you ship some standard jobs and procedures that will do this
for them and avoid the maintenance plans.
I don't really have any idea how widespread this issue is how much it would
actually take to implement this additional feature, but I can only assume
that it is not as big an issue as you think or MS is not aware of the
magnitude. If you wish to formally request a response and make them aware of
this issue I would raise a support call.
John
"Axel Bender" wrote:

> Thanks for the answer, John.
> Although I generally agree with you in saying that a compatibility mode
> should not be used on a long-term basis, I have to make some additional
> points on that topic:
> a) We have deployed a lot of CM 70 databases; changing the compatibility
> mode for them would not break our application, but it would give the
> users very slow response times in parts of the app (this is due to the
> change MS made to the query optimizer when it comes to selecting
> indexes). We know we have to make changes to the app, but frankly, we
> simply cannot afford the time for this now.
> b) Most of our customers are not able to write maintenance procedures
> (nor are all of our supporters).
> c) There is no common scheme that our users follow when it comes to
> backing up and checking their databases.
> d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
> 70 ones?
> I think, that supporting CM 70 databases would not be a big deal for MS;
> for us it would be.
> Kind regards,
> Axel
>