Condition in Select - SQL

Asked

Viewed 556 times

5

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

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

In that case I should take as a return only the priority 1 and 4. That is when there are items for priority 01 in stock it returns if there is no priority 02 must be returned. The data is already stored as pairs in the database, that is, I always need to look at the priority 1 and 2, then priority 3 and 4, my question is I can in a select command already perform this filter bring to priority 02 only if the priority 01 is zeroed in stock, bring the priority 04 only if the priority 03 is zeroed in stock and so on?

Today mine Query this way:

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

However I need to change to adapt the above mentioned need.

Table structure:

inserir a descrição da imagem aqui

sql database select h2database

  • What is the database manager: Oracle Database, SQL Server, Mariadb, Postgresql ...?

  • Where is the column that informs the amount that should be sent to the customer? That is, and if the amount requested by the customer is greater than what is in stock?

  • @Josédiz is H2 the database, is this question of the quantity requested does not interfere pro I need

  • Tried with a subselect and NOT EXISTS !?

  • @Motta could give an example?

  • Publish the real structure of the table and its keys as @José Diz said

  • @Motta changed the question with the table structure

  • Look here http://www.h2database.com/html/grammar.html#case_when in the documentation in the database

Show 3 more comments

1 answer

1


Santos,

I believe the solution to your problem is the use of NOT EXISTS, in each query and Union of all.

NOT EXISTS

NOT EXISTS functions as EXISTS, except for the WHERE clause in which it is used to be serviced if no line is returned by the subconsulta. For example, to locate product names that are not in the wheel subcategory:

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

Namely the not exists usually comes with a validation in another table. In the case of the example it will not bring if you have a subcategory with the name name.

source: https://technet.microsoft.com/pt-br/library/ms184297(v=sql.105). aspx

Example

Abstracting your problem, to be able 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 it in the other selections.

Browser other questions tagged sql database select h2database

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