Select Mysql with 3 arrays

Question

Select Mysql with 3 arrays

Asked 6 years, 6 months ago

Viewed 79 times

1

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

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

I create the following entries $year = ('17', '18'), $number = ('5673', '6790') and $Parc = ('1', '1'), when performing the 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

Since what I desire 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 entry of the "array" $numero, associated with the entry of the "array" $Parc.

I don’t quite understand what you want. What do you mean in cada entrada da "array" ? You say you need it to bring in the order you’re in $ano = ('17', '18'), $numero = ('5673', '6790') e $parc = ('1', '1') ? That is, 17 - 5673 - 1 and 18 - 6790 - 1 ? If so, it is wrong to use IN. If you have one array, loop it by filtering 1 to 1. This $ano = ('17', '18') is not an array. It would be $ano = array('17', '18'), and then I could do the foreach.

– rbz

2018/05/05 at 12:18

1 answer

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.

by Diego Rafael Souza • **2,900** points · Answer 1 · 2018-05-05T21:30:36+00:00

If I understand correctly you’re wanting every column match To within the whole X is in the same position in the set as the next column B in the other set Y.

In SQL there is no way you express this, so I would start from a different approach.

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

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

To not deteriorate so much the performance of the consultation in the bank, I would also keep the filters IN in isolated columns only with different values for each field. At 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 on SQL Fiddle.

I hope I’ve helped.