Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
9
Erster Beitrag:
vor 1 Jahr, 1 Monat
Letzter Beitrag:
vor 1 Jahr, 1 Monat
Beteiligte Autoren:
Michael Drechsel, Fabrice Harari, DerekT, kingdr, Curtis

SQL Problem (Multiple values for the "IN" statement)

Startbeitrag von Michael Drechsel am 30.06.2016 19: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)

Antworten:

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')

von Curtis - am 30.06.2016 20:55
Hi Curtis,

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

The user can choose one or many values. The field can have 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 contains 'Montpellier'
or CUSTOMER.City contains 'Avignon'
or CUSTOMER.City contains '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.

von Michael Drechsel - am 01.07.2016 05:30
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

von Fabrice Harari - am 01.07.2016 11:43
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 ....

von Michael Drechsel - am 01.07.2016 11:50
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

von kingdr - am 01.07.2016 11:58
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

von Fabrice Harari - am 01.07.2016 13:17
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 ...)

von Michael Drechsel - am 01.07.2016 13:29
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.

von DerekT - am 01.07.2016 19:35
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.