select dynamic

Navigation
2

Good afternoon!

I have a table something 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 records where:

field_name = 'field_1' and value = 9

I / O ????

field_name = 'field_2' and value = 10

but that the item_id column is in common with the records returned based on the above line.

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 query's "assembly" are "field_1, value 9 and field_2, value 10"

Thank you.

    
asked by anonymous 28.07.2016 / 20:43

4 answers

2

Solution 1 - Using the IN operator: 

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

Solution 2 - Using the 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 ))
    
28.07.2016 / 21:00
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
    
29.07.2016 / 13:33
2

If I understood correctly of what to do for this SQL: 

select * from tabela where
(
    nome_campo = 'campo_1' AND
    valor = 9
) OR (
    nome_campo = 'campo_2' AND
    valor = 10
);
    
28.07.2016 / 20:46
1

You can do this as follows; 

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
    
28.07.2016 / 20:55
How to make a daemon for the command "mongod" Query with calculation returning error in condition