CODEWITHSUNDEEP
sqlsql-server
Polina F.
Polina F.

Reputation: 649

sql check that all rows have only certain values

I have two tables: Invoices and InvoiceDrafts.

In Invoices there is a column called InvoiceDraftNumber corresponding to InvoiceDraftNumber in the InvoiceDraft table.

If invoice used an invoice draft then the invoice draft number will appear in Invoice table in column InvoiceDraftNumber. If invoice didn't use any invoice draft then null will appear in Invoice table in column InvoiceDraftNumber. Invoice can be in status sent or rejected. Invoice draft can be used only once , but if invoice that used it was rejected , then invoice draft can be used again. I need to check if invoice draft was already used by checking in this way :

I have a big working query from several selects - select on select , BUT I want something more elegant.

This is what I came up to , but I think there is some problem in my query:

SELECT COUNT(Invocies.ID)
FROM InvoiceDrafts DRAFT 
LEFT OUTER JOIN Invoices INVOICE on DRAFT.InvoiceDraftNumber = INVOICE.InvoiceDraftNumber
WHERE DRAFT.InvoiceDraftNumber = '12345'
  AND (INVOICE.UID is null
       OR INVOICE.Status not like 'Rejection')
GROUP BY Status

The count is of rows that are not in status 'Rejection' , meaning if the count is 0 then I can use this invoice draft

Will this query give me the expected result?

Upvotes: 0

Views: 3249

Answers (1)

Turophile
Turophile

Reputation: 3405

No need to overthink it. I think you want something like this:

 SELECT COUNT(*)
 FROM   Invoices
 WHERE  InvoiceDraftNumber = '12345'
 AND    Status <> 'Rejection'

If it returns 0 you are OK to create an invoice with that number. If it returns 1, the number has already been used up, if it returns > 1 you have errors in your data.

Upvotes: 2

Related Questions