Code reduction in sql query

Asked

Viewed 72 times

-1

I created a query with the following objective: Select all the tools which the inventories theirs nay are gifts in all businesses. One Tool has one or more stocks, a stockpile belongs to only one enterprise.

I need to know if there is any way to do this query to avoid this code redundancy, I don’t know if I’m having a bad day so I couldn’t do better but what came out was this:

select * from ferramenta f
where f.obrigatorio = 1 and (f.codigo not in (select ferramenta_codigo
                                            from estoque
                                            where empresa_codigo = 2)
                         or f.codigo not in (select ferramenta_codigo
                                            from estoque
                                            where empresa_codigo = 3)
                         or f.codigo not in (select ferramenta_codigo
                                            from estoque
                                            where empresa_codigo = 4)
                         or f.codigo not in (select ferramenta_codigo
                                            from estoque
                                            where empresa_codigo = 5)
                         or f.codigo not in (select ferramenta_codigo
                                            from estoque
                                            where empresa_codigo = 6))                                               
order by f.codigo desc

There are only companies 2,3,4,5,6.

sql postgresql query

  • I see no need to use all these OR operators. Just pass the ID listing with an IN condition in the stock table query.

  • Postgresql and oracle tags have been added, which effectively is the database used?

  • If I add "in" it will look for anyone inside the "in" I need to bring everyone from the "in" or none, resulting in that code I made. I am wearing postgres

3 answers

0

Make sure the select below meets your needs:

select * 
  from ferramenta f 
 where f.obrigatorio = 1 
   and (select count(*) 
          from (select e.empresa_codigo
                  from estoque e
                 where e.empresa_codigo in (2,3,4,5,6)
                   and e.ferramenta_codigo=f.codigo) int
        )<5

0

You don’t need to use the or, just use only the instruction not in in the field empresa_codigo, in this way helps in the performance of its query and still meets what you asked, decreasing in its size, see the result below: 

select * from ferramenta f
where f.obrigatorio = 1 
and f.codigo not in (select ferramenta_codigo
                     from estoque
                     where empresa_codigo not in(2,3,4,5,6))                                             
order by f.codigo desc

  • Did not solve, for this example Voce gave me would only work if I had a company with any number except these.... What would not work because the company code is FK, as an example I created a tool and added it in companies 2,3,4,5 and did not add in 6 and the result was the same as before...

0

Maybe in this way (I think I got the point right):

SELECT      F.* 
FROM        ferramenta  F
LEFT JOIN   estoque     E ON E.ferramenta_codigo = F.codigo
WHERE       F.obrigatorio = 1
        AND (
                    E.empresa_codigo <> 2
                OR  E.empresa_codigo <> 3
                OR  E.empresa_codigo <> 4
                OR  E.empresa_codigo <> 5
                OR  E.empresa_codigo <> 6
            )
ORDER BY    F.codigo DESC

Browser other questions tagged sql postgresql query

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