1

Topic: Sampling of records on intersection of field values and line

Good afternoon.
There is a table ` agents ` in which there is a string field, for example ` business `.
The whole natural numbers divided by a comma are stored in this field - "10,15,25,7,1".
Numbers in this field do not repeat.
Sortings of numbers in this field are not present, but, if needed, it is possible to make.
String constant Str which on value is similar with the above described field is set - i.e., in it the whole natural numbers divided by a comma are written down. Numbers do not repeat also sortings are not present (but it is possible to make).
It is necessary to select all records from ` agents ` in which the field ` business ` contains at least one number entering in Str.
Whether there is a possibility to make sampling by one request?
Thanks!

2

Re: Sampling of records on intersection of field values and line

OlegROA;
...  not  for SKL, better
To normalize, but if wish, yes, it
It is possible to make:
Was specific and still is  with similar tasks:
http://www.sql.ru/forum/1129063/faq-obr … vami-mysql

3

Re: Sampling of records on intersection of field values and line

OlegROA wrote:

if needed, it is possible to make

Actually there is a necessity to normalize given fields agents.business - tell, instead of it to add the table business with fields agent_id and business. In the first to store the identifier of the agent, and in the second values business for it on one on record (i.e. to one agent there will correspond some records in this table).
Then the request becomes absolutely elementary.

4

Re: Sampling of records on intersection of field values and line

The strange people - the question is set specific, and instead of answers - councils from the Whip!
As to the answer "javajdbc" - alas, absolutely simple case - search of one number in the list of numbers there is considered.
At me - search of a dial-up of numbers in other dial-up.
From this arguings it is possible to borrow only a method creation of a dial-up of natural numbers 1,2,3... And with their help to produce search of one of lists what then already to search for the selected number in other list.
But, besides, this method is restricted by an amount of numbers in the list and, , not the beautiful.
In general, the task is reduced to a question - fast search of intersection of two lists in the form of string fields.
Even is even easier - the fact of intersection of these lists, without creation of sampling of identical values from these lists.

5

Re: Sampling of records on intersection of field values and line

OlegROA wrote:

fast search of intersection of two lists in the form of string fields

Ah to you also the fast?
Without variants.
Any shifts - guaranteed seqscan.
Result the circuit in a normal type. Then the intersection is searched quickly and well. For applications which  too expensive - hang up sideways pair of triggers. The iterator on a line with selection of numbers to write in mysql all the same it is possible.
Or move on postgresql. To replace data type with an array, yes to add gin an index. Or if roughly restricts stupid application - it is possible even to leave a line, and an index to construct on string_to_array

6

Re: Sampling of records on intersection of field values and line

I thought, what already answered concerning sendings to the Whip? Once again - change of structure of a DB is impossible!
In general, managed  which defines intersection of two lists - on destination tables (which size to increase any more will not be) works bright.
All thanks!

7

Re: Sampling of records on intersection of field values and line

OlegROA wrote:

I thought, what already answered concerning sendings to the Whip?

...You will always send on it and everywhere if you want
Something to make in SKL... But do not understand that your task
Not "" for SKL... It not ,  is normal...
All  to the Whip, instead of to us....

OlegROA wrote:

Once again - change of structure of a DB is impossible!

...You tell it the first time...  time you invented...

OlegROA wrote:

in general, managed  which defines intersection of two lists

...The approach which I gave you - makes too most as ...
even faster... And  and search on variables -
Equally "not Knutovsky"... Simply  two different extensions SKL...

OlegROA wrote:

As to the answer "javajdbc" - alas, absolutely simple case

there is considered
... the iterator...  the cursor...  - the decision on  a question...
...So you is "alas inappropriate...

8

Re: Sampling of records on intersection of field values and line

OlegROA wrote:

All thanks!

...Always please...