Web Wiz - Green Windows Web Hosting

  New Posts New Posts RSS Feed - Reverse IN( ) sql statements???
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Reverse IN( ) sql statements???

 Post Reply Post Reply
Author
FLATLINE View Drop Down
Groupie
Groupie
Avatar

Joined: 08 July 2002
Location: Israel
Status: Offline
Points: 142
Post Options Post Options   Thanks (0) Thanks(0)   Quote FLATLINE Quote  Post ReplyReply Direct Link To This Post Topic: Reverse IN( ) sql statements???
    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: DarkGreen HQ
Back to Top
scottage View Drop Down
Newbie
Newbie


Joined: 20 August 2004
Location: United Kingdom
Status: Offline
Points: 15
Post Options Post Options   Thanks (0) Thanks(0)   Quote scottage Quote  Post ReplyReply Direct Link To This Post 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

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?

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.


Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.