| |
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. |
| |
| |
| | | |
|
| | |