CODEWITHSUNDEEP
sqlfirebirdexists
jordy233
jordy233

Reputation: 11

Using exists in sql query

I only want to show orders which need to be invoiced. In the picture below, I showed two orders. I only want to show offerno 2200, because there is an amount (5000) that need to be invoiced. In both offerno's there is a row with termtype 6. This must be ignored.

I used the query:

Select
    salesorder.offerno,
    salesorderterm.termtype,
    Salesorderterm.invoiceamount,
    salesorderterm.salesinvoice
From 
    salesorder
Left Join 
    salesorderterm on (salesorder.objectid = salesorderterm.salesorder)
where 
    exists (select 1 from salesorderterm where salesinvoice = 0)
    and salesstatus = 1

However, the output shows both orders. This might be because of the row with termtype 6 and invoiceamount = 0.

How can I solve this?

Attachment: Example of data

Upvotes: 1

Views: 2977

Answers (1)

Crick3t
Crick3t

Reputation: 1151

The filter in the inner select is missing and it just checks if there are any salesorderterm with zero salesinvoice. You need to add salesorder.objectid = salesorderterm.salesorder there too

Select
salesorder.offerno,
salesorderterm.termtype,
Salesorderterm.invoiceamount,
salesorderterm.salesinvoice
From salesorder
Left Join salesorderterm on (salesorder.objectid = salesorderterm.salesorder)
where exists (select 1 from salesorderterm where salesinvoice = 0 and salesorder.objectid = salesorderterm.salesorder)
and salesstatus = 1

But probably it is better if you just remove the exists and just add it as a filter.

Select
salesorder.offerno,
salesorderterm.termtype,
Salesorderterm.invoiceamount,
salesorderterm.salesinvoice
From salesorder
Left Join salesorderterm on (salesorder.objectid = salesorderterm.salesorder)
where salesinvoice = 0
and salesstatus = 1

Upvotes: 1

Related Questions