CODEWITHSUNDEEP
sql
Kim HJ
Kim HJ

Reputation: 1192

SQL Finding result in one table based on a second table

I have two tables and I need to find all invoice that have a ready day of today and have no period in the INumber and all items have not been delivered. Each Invoice may have several rows in Invdetails and I have to find all rows that have a quantity that is not zero.

Invoice
Field String: INumber
Field Date: DateReady

InvDetails
Field String: INumber
Field Integer: Quantity

Here is my SQL, but it takes long time and sometimes hang I don't know if there is a better way.

Select D.* from InvDetails D
Join Invoice I on on D.INumber=I.INumber
where I.Dateready='2019-05-14' and D.Quantity > 0 and I.INumber IS NOT LIKE '%.%'

Thanks for any help.

Upvotes: 0

Views: 35

Answers (2)

Michael Dougan
Michael Dougan

Reputation: 1698

You can also try scoping the tables down before doing the join, so, you'll be looking at fewer rows...

SELECT D.*
FROM (SELECT * FROM Invoice WHERE Dateready = ‘2019-05-14’ AND CHARINDEX(‘.’, INumber) = 0) AS I
LEFT JOIN (SELECT * FROM InvDetails WHERE Quantity > 0) AS D ON I.INumber = D.INumber

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Assuming this query is the correct logic:

select D.*
from InvDetails D join
     Invoice I 
     on D.INumber = I.INumber
where I.Dateready = '2019-05-14' and
      D.Quantity > 0 and
      I.INumber IS NOT LIKE '%.%';

You want indexes on:

create index idx_invoice_dateready_inumber on (dateread, inumber);

create index idx_invoicedetails_inumber_quantity on (inumber, quantity);

This should significantly improve the performance of your query.

Upvotes: 0

Related Questions