How do I write a select statement to see if table field is in variable list

[Guest]

I was wondering if someone knew the best way to write a query to pull back rows where a table field is found in a a variable string.  I'm using Cold Fusion 10 on Microsoft SQL Server.

The table has a series of fields such as

carcode          carname

G                Garage

C                Carport

A               Attached

D               Detached

And another query pulls back a field that stores as text all the abbreviations that apply

I want to pull back all the carnames that would match the abbreviations stored in that field.  I thought I could use a contains line but that does not work.  How do I pull back matches against a variable string... I tried an IN statment but that didn't work.

CFQUERY...

Select carname from CarTable WHERE CONTAINS (carcode, '#query.carlist#') does not work. 

   select carname from CarTable WHERE CONTAINS(carcode, 'G,A')

I'm stumped.  The list of abbreviations could be very LOOOONG or short and I just need to translate those abbreviations into the full names by grabbing it from another table.  It isn't just one abbreviation so I can't use LIKE in the one to one sense.

Thank you so much.

[Dan_Bracuk]

The in statement that didn't work - what was it and what happened when you ran it?

[Guest]

select carname from CarTable

WHERE CarTable.carcode in ('#queryname.car_storage#')

It doesn't give me an error, but it does bring back zero records and I have confirmed that the content in the database should be a match.

I'm outputting the recordcounts found and the sring that is in #queryname.carstorage#

G,A

0

No records, but the letters match the letters in the database.  Hmmmm... I am guessing it is something simple that I am missing.

Thanks

[Dan_Bracuk]

If you have records with these values,

G,A

0

then your first mistake is that you designed your database poorly.  Storing lists in a single field is essentially stroring unuseable data.  A one to many relationship is better.  If you don't know what that is, I've heard good things about the book, Database Design for Mere Mortals.

Next, this syntax, #queryname.car_storage#, only returns the first record.  To get all the records, use the valuelist function.

Next, in sql, if you have a list of strings, each one has to be quoted.  Something like this.

where myfield in ('a', 'b', 'c')

The best way to achieve this in ColdFusion is to use the cfqueryparam tag with the list attribute set to yes.

[Guest]

Thank you.  I wish I had been the designer of the database but this is part of where I have to talk to the old database system.  Until 2014 this is what I get to work with.  Thanks for the advice on cfqueryparam, I'll give that a shot.