6 Replies - 1643 Views - Last Post: 13 January 2012 - 04:06 PM Rate Topic: -----

#1 jk145  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 09-January 12

Querying for multiple values

Posted 09 January 2012 - 04:38 PM

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?
Is This A Good Question/Topic? 0
  • +

Replies To: Querying for multiple values

#2 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1889
  • View blog
  • Posts: 3,428
  • Joined: 13-January 08

Re: Querying for multiple values

Posted 09 January 2012 - 04:57 PM

View Postjk145, 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

Was This Post Helpful? 0
  • +
  • -

#3 jk145  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 09-January 12

Re: Querying for multiple values

Posted 09 January 2012 - 07:19 PM

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.
Was This Post Helpful? 0
  • +
  • -

#4 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1889
  • View blog
  • Posts: 3,428
  • Joined: 13-January 08

Re: Querying for multiple values

Posted 10 January 2012 - 06:36 AM

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!
Was This Post Helpful? 0
  • +
  • -

#5 jk145  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 09-January 12

Re: Querying for multiple values

Posted 12 January 2012 - 07:16 PM

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.
Was This Post Helpful? 0
  • +
  • -

#6 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1889
  • View blog
  • Posts: 3,428
  • Joined: 13-January 08

Re: Querying for multiple values

Posted 13 January 2012 - 06:49 AM

View Postjk145, 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!
Was This Post Helpful? 0
  • +
  • -

#7 jk145  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 09-January 12

Re: Querying for multiple values

Posted 13 January 2012 - 04:06 PM

View PostCraig328, on 13 January 2012 - 06:49 AM, said:

View Postjk145, 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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1