PC SOFT

FORUMS PROFESSIONNELS
WINDEVWEBDEV et WINDEV Mobile

Accueil → WINDEV 2024 → SQL Problem (Multiple values for the "IN" statement)
SQL Problem (Multiple values for the "IN" statement)
Débuté par Michael Drechsel, 30 juin 2016 21:42 - 9 réponses
Posté le 30 juin 2016 - 21:42
Hi,

is there a simple solution for this ?

SELECT CUSTOMER.CustomerLName
FROM CUSTOMER
WHERE CUSTOMER.City IN ('Montpellier', 'Avignon', 'Marseille')


Customer.city could be 'Montpellier' or 'Avignon;Marseille'

(multiple selection in a table)
Posté le 30 juin 2016 - 22:55
I don't quite understand your question. If 'Montpellier' and 'Avignon;Marseille' are the 2 values to search then the following will work.

SELECT CUSTOMER.CustomerLName
FROM CUSTOMER
WHERE CUSTOMER.City IN ('Montpellier', 'Avignon;Marseille')
Posté le 01 juillet 2016 - 07:30
Hi Curtis,

I need something like a "many to many" SQL Statement.

The user can choose one or many values.

Until now I split the list in pieces, so that the SQL statement is


SELECT CUSTOMER.CustomerLName
FROM CUSTOMER
WHERE CUSTOMER.City ='Montpellier'
or CUSTOMER.City ='Avignon'
or CUSTOMER.City ='Marseille')

but you have to know the maximum of the values for the "or" construct if you use the query editor.

Maybe there is a a SQL command for that or I have to build my own statement without the query editor.
Posté le 01 juillet 2016 - 13:43
Hi Michael,

I'm not sure I understand the problem...

In the query editor, when adding the condition, just use the "is in the list" one, and prepare a string with your list of cities that you pass as parameter...

Best regards
Posté le 01 juillet 2016 - 13:50
Ok,

I try to explain:

My Database has a field with the following values:

Berlin
Paris
London
Paris;London
Berlin;Paris;London


The user has a Window to select one or more citys. He choose:
Berlin;London

How can I define a SQL Statement that returns all records where Berlin and London is included ?
I think, thats not possible with one SQL Statement ....
Posté le 01 juillet 2016 - 13:58
Hi

Try this:

SELECT CustomerLName FROM CUSTOMER as a
WHERE a.City IN ('Montpellier', 'Avignon', 'Marseille')

or

SELECT CustomerLName FROM CUSTOMER
WHERE City IN ('Montpellier', 'Avignon', 'Marseille')

as it's a standard SQL syntax or could be sqlTranslation bugs and
I've been using such many times in hfSQL with correct resultSets.

HTH

King
Membre enregistré
102 messages
Popularité : +2 (2 votes)
Posté le 01 juillet 2016 - 14:41
How about this:

SELECT CustomerLName FROM customer WHERE ( city LIKE '%London%' OR city LIKE '%Berlin%' )


--
http://arnoldconsult.de
WinDev 21 with Oracle, WinDevMobile Android Apps and Motorola Windows CE Mobile Barcode Scanners, Python 3 with Oracle and MySQL
Posté le 01 juillet 2016 - 15:17
Hi again

so you are trying to use a contain X AND contain Y AND contain Z, with an unknown number of X Y Z....

hmmmm

One thing is sure, that is going to be a VERY SLOW query, as ALL records of the file will have to be read...

I would instead reorganize the DB and create a citylink file and have as many link records are there are cities for the main record...

Best regards
Posté le 01 juillet 2016 - 15:29
Hi Fabrice,

thats true.

I can define a query (not in the Query-Editor), but I hate that :-(

1. Split the User-choosen cities and load it in a array (arrUserCity)
2. Build a Query

SELECT City FROM CUSTOMER
WHERE
(Customer.City contains arrUserCity[1] or Customer.City contains arrUserCity[2] or ...)
Posté le 01 juillet 2016 - 21:35
Michael

As you say you can split the user chosen city strings and load each value in an array.

Loop through the array - for each record build this into a string variable (Berlin,Paris,London) and pass this to the query for the 'Contains' parameter.

If your users can select duplicate cities then consider using an associative array with the 'WithoutDuplicates' option.
The lower the number of options presented to the query the better.

I have used this method - it works seamlessly in the blink of an eye.