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

​x
 
InvoiceField String: INumberField Date: DateReady​InvDetailsField String: INumberField 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 '%.%'

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

Thanks for any help.

Answer

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

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

Advertisement

Answer