select dinamico

Asked

Viewed 118 times

2

good afternoon!

I have a table like this:

id   |   valor   | nome_campo     |  item_id (fk)
----------------------------------------------
1    |    9      | campo_1        |  22
2    |    10     | campo_2        |  21
3    |    2      | campo_2        |  22
4    |    3      | campo_2        |  21
5    |    3      | campo_1        |  22
6    |    9      | campo_2        |  26
7    |    9      | campo_1        |  22
8    |    9      | campo_3        |  25
9    |    10     | campo_4        |  27
10   |    9      | campo_1        |  24
11   |    10     | campo_2        |  22
12   |    10     | campo_2        |  24
13   |    10     | campo_2        |  22
14   |    10     | campo_1        |  21

I need to list all the records where:

field_name = 'field_1' and value = 9

AND / OR ????

field_name = 'field_2' and value = 10

but that the item_id column is in common to the records returned based on the lines above.

The expected result is this:

 
id   |   valor   | nome_campo  |  item_id (fk)
----------------------------------------------
1    |    9      | campo_1     |  22
7    |    9      | campo_1     |  22
11   |    10     | campo_2     |  22
13   |    10     | campo_2     |  22
12   |    10     | campo_2     |  24
10   |    9      | campo_1     |  24

The values that are passed to the "mount" of the query’s Where are "field_1, value 9 and field_2, value 10"

Thank you.

mysql

4 answers

2

Se eu entendi certo da para fazer por esse SQL:

select * from tabela where
(
    nome_campo = 'campo_1' AND
    valor = 9
) OR (
    nome_campo = 'campo_2' AND
    valor = 10
);

  • I answered the question because I didn’t have room

2

Solution 1 - Using the operator IN:

SELECT
    id, valor, nome_campo, item_id
FROM
    tabela
WHERE
    (nome_campo, valor) IN ( ('campo_1',9), ('campo_2',10) );

Solution 2 - Using logical operators AND and OR

SELECT
    id, valor, nome_campo, item_id
FROM
    tabela
WHERE
    (( nome_campo = 'campo_1' AND valor = 9) OR ( nome_campo = 'campo_2' AND valor = 10 ))

  • So I used both ways worked but not the way I wanted because it brings the data whose values are equal to 9 or equal to 10.

  • I answered the question because I didn’t have room

2

You can use this method will work:

select * from tabela where
nome_campo = 'campo_1' AND valor = 9
union all
select * from tabela where
nome_campo = 'campo_2' AND valor = 10
  • 2

    Yeah, it’ll work, but it’s not even remotely a good answer.

  • But it didn’t work he keeps bringing me one or the other , I had even tried with Union and I ended up forgetting Union all ... but also not yet the expected result ... thanks for the reply

  • using Union all, displays here the result that Query is bringing ..

1

You can do it this way;

declare @tabela  table
(
   id int,
   valor int,
   nome_campo varchar(20), 
   item_id INT  
)

insert into @tabela values
(1,9,'campo_1'  , 22),
(2,10,'campo_2'  , 21),
(3,2,'campo_2'  , 22),
(4,3,'campo_2'  , 21),
(5,3,'campo_1'  , 22),
(6,9,'campo_2'  , 26),
(7,9,'campo_1'  , 22),
(8,9,'campo_3'  , 25),
(9,10,'campo_4'  , 27),
(10,9,'campo_1'  , 24),
(11,10,'campo_2'  , 22),
(12,10,'campo_2'  , 24),
(13,10,'campo_2'  , 22),
(14,10,'campo_1'  , 21)


select * from @tabela
where (valor = 9 and nome_campo = 'campo_1')
or (valor = 10 and nome_campo = 'campo_2')
order by item_id

  • I answered the question because I didn’t have room

Browser other questions tagged mysql

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