Select MySQL with 3 arrays

Question

Select MySQL with 3 arrays

Navigation

#1 by (1 votes)

1

I have a MySQL table where the primary entries are 3 columns: YEAR, NUMBER, and PLOT. Home The column NUMERO is a sequential numbering for each YEAR, however it is possible that for a same number there are several PLOTS. I need to perform a SELECT with arrays, where I have an array for YEAR, another for NUMBER and another for PLOT. The fact is that if you use "IN", it does not have the number associated with YEAR and PLOT. For example:

ANO NUMERO PARCELA
17  5673   1
17  6783   1
18  5673   1
18  6790   1

And I create the following entries $ year = ('17', '18'), $ number = ('5673', '6790') and $ parc = ('1', '1'), following query:

SELECT * FROM table WHERE ANO IN($ano) AND NUM IN($numero) AND PARC IN($parc)

The result will be:

17 -> 5673 -> 1
18 -> 5673 -> 1
18 -> 6790 -> 1

Being that what I want is:

17 -> 5673 -> 1
18 -> 5673 -> 1   <---- SEM ESSE RESULTADO
18 -> 6790 -> 1

That is, I want each entry of the array $ year to be associated with the input of the $ array, associated with the input of the array $ parc.

mysql

asked by anonymous 05.05.2018 / 13:17

1 answer

Is SQL Express free? Graphs algorithm to solve Sudoku

score 1 · Answer 1

If I understand correctly you are wanting each column match A within the set X to be in the same position in the set as the match in the next column B in the other Y set.

In SQL there's no way you can express this, so I'd take a different approach.

The data you want to use as a filter is actually the combination of year, number, and plot values. That is, according to its example, a combination would be 17 + 5670 + 1 and the other 18 + 6790 + 1 .

Obviously we would treat this information as text to avoid overlapping in the sum and I would also add a separator character between the data.

In order not to deteriorate the performance of the query in the database, I would also keep the IN filters in the isolated columns only with the different values for each field. In the end, your query would look like this:

SELECT *
FROM table t
WHERE  t.ano IN ( 17,18 ) AND 
   t.numero IN ( 5673, 6790 ) AND
   t.parcela IN ( 1 ) AND 
   Concat( t.ano, '#', t.numero, '#', t.parcela ) IN ( '17#5673#1', '18#6790#1' )

See this example working in SQL Fiddle .

I hope I have helped.