Classic ASP Discussion - Reverse IN( ) sql statements???

Print Page | Close Window

Reverse IN( ) sql statements???

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=11610
Printed Date: 31 March 2026 at 3:01am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Reverse IN( ) sql statements???

Posted By: FLATLINE
Subject: Reverse IN( ) sql statements???
Date Posted: 23 August 2004 at 7:34pm

I need to do the exact opposite of the IN() sql statement.
I have two tables in my db:

Table1 has a string field which contains strings of ID Numbers of the records from Table2 seperated with commas, for example:

rs1("string") = "2,5,1,7"

Then, when I want to query all the mentioned records from Table2 I simply write:

strSQL = "SELECT * FROM tbl2 WHERE row_id IN(" & rs1("string") & ")"

Now I want to do the exact opposite: Have an ID Number from Table2 and query all the records from Table1 that have that ID Number in their string. I can't use the WHERE LIKE statement because there's a problem with the edge cases (lets say the row_id I'm looking for is 2):

If I look for "WHERE string LIKE '%" & row_id & "%'", I will have problems with such strings: "6, 22, 42"
If I look for "WHERE string LIKE '," & row_id & ",", I will have problems with such strings: "2, 5, 3" and "6, 7, 2"
If I look for "WHERE string LIKE '," & row_id & "'" or "WHERE string LIKE '" & row_id & ",'", I will have problems with such strings: "5,23" and "26,8"

So I don't know how to query these things. Any help please?

Sorry if I explained it too complicatedly.

-------------
Visit my site: http://darkgreen.service-club.net - DarkGreen HQ

Replies:

Posted By: scottage
Date Posted: 26 August 2004 at 3:30pm

Hi Flatline,
this sounds a little confusing but I'd like to help if I can.
Table1 has a column "string" that contains a comma separated list of values, each of which is the value of the "ID" column in Table2. So, given a value for "ID" in Table2 you'd like the corresponding rows from Table1 where "string" contains the given value?
How about select * from Table1 where ID IN([string]) I'm not really sure if this is what you need ... it should work out to select * from Table1 where 2 IN("2,5,4,43"). I haven't tested this though. Let me know how you get on.

Scott
http://www.realwebdevelopers.com - http://www.realwebdevelopers.com

P.S. It sounds like you're database structure cound be reviewed. Would it make more sense to add a column to Table2 indicating it's parent record in Table1 ... rather than this is a record in Table1 and these are it's associated records in Table2, why not these are records in Table2 and these are the parent records in Table1?