Android Question [Solved] SQL - IN operator

udg · Jun 29, 2020

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:

B4X:

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 · Jun 29, 2020

udg said:
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:

B4X:

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 · Jun 29, 2020

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

udg · Jun 29, 2020

@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 · Jun 29, 2020

udg said:
can you please show an example?

SQL:

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

aeric · Jun 29, 2020

Or you can select values from another table

SQL:

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 · Jun 29, 2020

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

Android Question [Solved] SQL - IN operator

udg

Expert

Mahares

Expert

aeric

Expert

udg

Expert

aeric

Expert

aeric

Expert

udg

Expert

Similar Threads

Android Question [Solved] SQL - IN operator

udg

Expert

Mahares

Expert

aeric

Expert

udg

Expert

aeric

Expert

aeric

Expert

udg

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency