Condition in Select - SQL

Question

Condition in Select - SQL

Navigation

#1 by (1 votes)

5

I have a table where the priorities of output of the supplies per customer are stored, for example:

cliente | codigosuprimento | prioridade | quantidaestoque
    1   |        500       |      1     |        20
    1   |        501       |      2     |        10
    1   |        502       |      3     |        00
    1   |        503       |      4     |        15

In this case I should only return 1 and 4 priority. That is, when there are items for priority 01 in stock it returns if priority 02 is not to be returned. The data is already stored as pairs in the database, ie I need to always look for priority 1 and 2, then priority 3 and 4, my question is do I get in a select command already performing this filter to bring the priority 02 only if priority 01 is zeroed in inventory, bring priority 04 only if priority 03 is zeroed in inventory and so successively?

Today my Query looks like this:

SELECT s
FROM SuprimentosPedidos s
WHERE s.codigoModeloImpressora= :codigoModeloImpressora and 
s.codigoEmpresa= :codigoEmpresa
ORDER BY s.prioridadeSaida, s.suprimento

But I need to change it to fit the need cited above.

Table structure:

sql select h2database

asked by anonymous 04.10.2017 / 22:36

1 answer

What is Flyway and when to use it? Async Methods - return string from UDP

score 1 · Accepted Answer

Saints,

I think the solution to your problem is the use of NOT EXISTS , in every query and union of all.

NOT EXISTS

NOT EXISTS works as EXISTS except for the WHERE clause where it is used to be serviced if no rows are returned by the subquery. For example, to find product names that are not in the wheels subcategory:

SELECT Name
FROM Production.Product
WHERE NOT EXISTS
    (SELECT * 
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID = 
            Production.Product.ProductSubcategoryID
        AND Name = 'Wheels')

In other words, not exists is usually accompanied by a validation in another table. In the case of the example it will not show if it has a subcategory named name.

source: link

Example

Abstracting your problem, to show the solution

SELECT s
  FROM SuprimentosPedidos s
 WHERE s.Prioridade = 4 AND
       NOT EXISTS(
SELECT s
      FROM SuprimentosPedidos s
     WHERE s.Prioridade = 3
)

And do this in the other selections.