[jk145]

I am currently writing code that is supposed to query for several different records with multiple IDs. I have tried:

SELECT *
FROM Items
WHERE ID = ( 1, 2 )

SELECT *
FROM Items
WHERE ID = 1, 2

SELECT *
FROM Items
WHERE ID = '1, 2'

What am I doing wrong?

[Craig328]

jk145, on 09 January 2012 - 06:38 PM, said:

I am currently writing code that is supposed to query for several different records with multiple IDs. I have tried:

SELECT *
FROM Items
WHERE ID = ( 1, 2 )

SELECT *
FROM Items
WHERE ID = 1, 2

SELECT *
FROM Items
WHERE ID = '1, 2'

What am I doing wrong?

It's the SQL WHERE statement itself. Instead of = try IN in your first example and see how that works for you.

This post has been edited by Craig328: 09 January 2012 - 04:59 PM

[jk145]

That would work for the above, but what about when I want to query for the values 1 and 5 or 2 and 4? I am dynamically outputting this information using <cfoutput>, so it changes.

[Craig328]

It would work just as well. What you're looking for information on is the "IN" operator of most SQL languages (MySQL example here).

Basically, what you're doing is providing a series of values as a set (right side of the IN operator) and then asking the database to bring back records whose column you're querying about (the left side of the IN operator) that have one of the values in the set.

So, to answer your question, you'd need to be more specific about the logic. You said "what about when I want to query for the values 1 and 5 or 2 and 4?" The trouble with asking a question like that is that AND and OR are specific operators themselves and have value when writing a query so you'd need to be more clear about what kind of WHERE filter you're trying to apply.

If you need more help, post back here with a more detailed explanation for the type of WHERE filtering you're looking for and we can show you how to write the WHERE statement to satisfy it.

Good luck!

[jk145]

Thanks, I misunderstood IN to mean between.

How would I go about a query if i had a string of numbers, seperated by commas, in a column of a database, and i wanted to return all rows that had one specific number, like:

1, 5, 8
2, 4, 5
3, 4, 8

and I wanted it to return all of the rows that had the number 4 in it, regardless of the other numbers?

Any help would be appreciated.

[Craig328]

jk145, on 12 January 2012 - 09:16 PM, said:

Thanks, I misunderstood IN to mean between.

How would I go about a query if i had a string of numbers, seperated by commas, in a column of a database, and i wanted to return all rows that had one specific number, like:

1, 5, 8
2, 4, 5
3, 4, 8

and I wanted it to return all of the rows that had the number 4 in it, regardless of the other numbers?

Any help would be appreciated.

There's a couple of possible ways to do it but the one I'd probably look at first would be to use the LIKE operator. BTW, I'm giving MySQL links so if you're not using MySQL let us know.

Anyway, the LIKE operator is fairly handy. In your example above, a search for all records whose ID contain "4" would look like this:

SELECT *
FROM Items
WHERE ID LIKE '%4%'

The '%' is a wildcard for most commercial database LIKE operators. What that means is that the search will only bring back literal matches for records that contain a 4. If the LIKE condition was this: '4%' it would only return strings where the string started with a 4 and if it looked like this: '%4' it would return strings that ended in 4. You'll need to read that link and run some sample queries to understand the function of the wildcard operator if you're not already familiar with it.

That said, with the sample query I made above, you'll get returns for IDs that contain '4'. This means if the string of numbers contained something like '14', that will come back too. You'd need to refine your LIKE conditions or add additional LIKE conditions to limit it to records that contain just '4'.

Good luck!

[jk145]

Craig328, on 13 January 2012 - 06:49 AM, said:

jk145, on 12 January 2012 - 09:16 PM, said:

Thanks, I misunderstood IN to mean between.

How would I go about a query if i had a string of numbers, seperated by commas, in a column of a database, and i wanted to return all rows that had one specific number, like:

1, 5, 8
2, 4, 5
3, 4, 8

and I wanted it to return all of the rows that had the number 4 in it, regardless of the other numbers?

Any help would be appreciated.

There's a couple of possible ways to do it but the one I'd probably look at first would be to use the LIKE operator. BTW, I'm giving MySQL links so if you're not using MySQL let us know.

Anyway, the LIKE operator is fairly handy. In your example above, a search for all records whose ID contain "4" would look like this:

SELECT *
FROM Items
WHERE ID LIKE '%4%'

The '%' is a wildcard for most commercial database LIKE operators. What that means is that the search will only bring back literal matches for records that contain a 4. If the LIKE condition was this: '4%' it would only return strings where the string started with a 4 and if it looked like this: '%4' it would return strings that ended in 4. You'll need to read that link and run some sample queries to understand the function of the wildcard operator if you're not already familiar with it.

That said, with the sample query I made above, you'll get returns for IDs that contain '4'. This means if the string of numbers contained something like '14', that will come back too. You'd need to refine your LIKE conditions or add additional LIKE conditions to limit it to records that contain just '4'.

Good luck!

Thanks, yes I am using MySQL.

I am experimenting with using LIKE '% 4,%' with a space before the 4 to eliminate possibility of other numbers with a numeral that is the same.