Thread: Querying a list of values

Hi people,

Is there a / what is the way to query a list of values ?

some_list = '1,2,3,4,5'

some_list could also be null or any length.

<cfif some_list IS NOT NULL>

SELECT some_id, some_name
FROM some_table
WHERE some_id = any of the list values of some_list

</cfif>

So that that query would return five rows in this case ?

I can do a cfml-loop around the query according to the list length for each
value but that means a I do an x number of queries instead of just one.

??,

Aarni
--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--------------

Aarni ,


How about

SELECT some_id, some_name
FROM some_table
WHERE some_id  IN (YOUR LIST seperated with commas)

Or you may go for arrays see contib for it.

--
With Best Regards,
Vishal Kashyap.
Did you know SaiPACS is one and only PACS
Management tool.
http://saihertz.com,
http://vishalkashyap.tk

You might want to look at:

http://www.postgresql.org/docs/current/static/functions-comparisons.html

Sean

On Nov 18, 2004, at 7:38 AM, Aarni Ruuhimäki wrote:

> Hi people,
>
> Is there a / what is the way to query a list of values ?
>
> some_list = '1,2,3,4,5'
>
> some_list could also be null or any length.
>
> <cfif some_list IS NOT NULL>
>
> SELECT some_id, some_name
> FROM some_table
> WHERE some_id = any of the list values of some_list
>
> </cfif>
>
> So that that query would return five rows in this case ?
>
> I can do a cfml-loop around the query according to the list length for
> each
> value but that means a I do an x number of queries instead of just one.
>
> ??,
>
> Aarni
> --------------
> This is a bugfree broadcast to you
> from **Kmail**
> on **Fedora Core 2** linux system
> --------------
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org

That was quick !

Thanks,

Aarni

On Thursday 18 November 2004 14:38, Aarni Ruuhimäki wrote:
> Hi people,
>
> Is there a / what is the way to query a list of values ?
>
> some_list = '1,2,3,4,5'
>
> some_list could also be null or any length.
>
> <cfif some_list IS NOT NULL>
>
> SELECT some_id, some_name
> FROM some_table
> WHERE some_id = any of the list values of some_list
>
> </cfif>
>
> So that that query would return five rows in this case ?
>
> I can do a cfml-loop around the query according to the list length for each
> value but that means a I do an x number of queries instead of just one.
>
> ??,
>
> Aarni
> --------------
> This is a bugfree broadcast to you
> from **Kmail**
> on **Fedora Core 2** linux system
> --------------

Hi again,

And hmm, how about the other way round ?

SELECT *
FROM some_table
WHERE some_list contains a given value ?

Like in

WHERE some_list LIKE '%1%'

but only records that have '1' in the list and not '11', '12' etc ?

TIA,

Aarni

On Thursday 18 November 2004 14:49, Vishal Kashyap @ [SaiHertz] wrote:
> Aarni ,
>
>
> How about
>
> SELECT some_id, some_name
> FROM some_table
> WHERE some_id  IN (YOUR LIST seperated with commas)
>
> Or you may go for arrays see contib for it.


--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--------------