Android Question [Solved] SQL - IN operator

[udg]

Hi all,
is there a way in pure SQL to construct a WHERE clause using the IN operator where its list is of variable length?

I mean something like:

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

but with the Countries' list built at runtime and made up of any number of items (i.e 1 to 20).

I know that I could construct the whole statement once I've inspected the input parameters so to know beforehand how many "?" I should use on case-by-case basis.
But what about SQL language?

TIA

 

[Mahares]

but with the Countries' list built at runtime and made up of any number of items (i.e 1 to 20).

You can build your IN clause with StringBuilder like this:

Dim MyList As List
    MyList.Initialize
    MyList.AddAll(Array As String("Germany", "France", "UK", "Israel"))
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append( " IN(")
    For i= 0 To MyList.size -1
        Dim f As String =MyList.Get(i)
        If i = MyList.Size-1 Then
            sb.Append($"'${f}')"$)
        Else
            sb.Append($"'${f}',"$)
        End If
    Next
    Log(sb.ToString)  'displays: IN('Germany','France','UK','Israel')

 

[aeric]

I think you can use SELECT from a list of items with the use of UNION after the opening bracket.

 

[udg]

@Mahares : that's exactly what I meant with " I know that I could construct the whole statement once .."
@aeric : can you please show an example?

 

[aeric]

can you please show an example?

SELECT * FROM Customers
WHERE Country IN
(SELECT 'Germany'
UNION
SELECT 'France'
UNION
SELECT'UK')

 

[aeric]

Or you can select values from another table

CREATE TABLE `countries` (
  `country` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `countries` (`country`) VALUES
('Germany'),
('France'),
('UK');

SELECT * FROM Customers
WHERE Country IN
(SELECT country FROM Countries)

 

[udg]

OK, thanks.
So my options are:
- fill on the fly a temporary table and use it like you did on post #6
- follow the StringBuilder path and construct on the fly a Select statement based on the parameters received