SQL Question

[OneSolution]

Hi,

I'm not an SQL guru, and so I'm rather happy at how far I've got. However,
I am now completely stumped on how to answer this question.

Basically, my script accepts a string. Each word is separated by a space.
Some words can begin with a +, others can begin with a - and yet other don't
have to begin with either of these symbols, they can be the word by itself.

My script parses the string and tries to build an SQL statement out of it.
The script removes the + and - symbols. The idea is that the word adjoining
a + is a mandatory word, while a word adjoining a - cannot be included in
the result.

I have the following tables.

table1
Table1ID (PK)
Table1IDDescription

table2
table2ID (PK)
Table1ID (FK)
uid (FK)

userTable
uid (PK)
other fields



All words that don't have either a + or a - can be included in the results.
A standard or. This part works great, and I don't have any trouble with it.

All words that have a + in front of it MUST be in every single result. This
isn't working, and it's defying me.

All words that have a - in front of it CANNOT be in the result at all. The
way I did it, I got all the results except the ones with these words in it.
That's not what I want, I just want to exclude the records with these words
in it.

So anyhow, my SQL was formed with two nested selects:

select * from userTable where uid in (select uid from table2 where
table2.Table1ID in (select table1ID from table1 where table1IDDescription
like '%item1%' or table1IDDescription like '%item1%' or table1IDDescription
not like '%item1%'))


This SQL is autogenerated and the final where clause needs to account for
the +, - or lack thereof.

Can anyone offer me a suggestion?

Thanks,
Santosh Krishnan

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need to use JOINS instead of subqueries and you can use one
criteria to get what you want - no words with "-" prefix.

SELECT U.*
FROM (userTable As U INNER JOIN table2 As T2
ON U.uid = T2.uid) INNER JOIN table1 As T1
ON T2.table1ID = T1.table1ID
WHERE table1IDDescription Not LIKE "-*"

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBgzloechKqOuFEgEQLSvQCg8szrM/hRPnKf7TL8qel+eBmK0rgAn0jG
lCYIrj2BhXpGT7iJgue6op50
=pfFL
-----END PGP SIGNATURE-----


OneSolution wrote: