Link to home
Start Free TrialLog in
Avatar of DrDamnit
DrDamnitFlag for United States of America

asked on

MySQL Left Join Syntax to find missing records?

I have a table that has two types of data (will call them invoiced and paid). Each record is of one of two types: type1 (invoice) or type2 (payment record).

So, for an invoice (#1234) I have one record (with column invoice_number = 1234, type = 1) that shows an invoice was sent out, then another record (hopefully - if the client paid their invoice) that shows that it was paid (again, the column invoice_number shows 1234 for that record as well, but this type = 2 since it is a payment).

I want to run a query that shows me all the invoices that were sent out, but not yet paid.

I am thinking that it should be a left join query where I join the table to itself, but the variations I have tried so far don't seem correct because a count(*) on the invoice type records, I get around 8,000. When I do a count(*) for the payment type records, I get around 2,000. So, we are aware that this client has around 4,000 outstanding invoices. Ergo, I am looking for a result set right at 4,000 records.

What's the query?
Avatar of Gary
Gary
Flag of Ireland image

Generically...I think...

SELECT * from table AS t1
JOIN table t2 ON t1.column invoice_number =t2.column invoice_number 
WHERE t1.type = 1 AND t2.type = Null

Open in new window

Avatar of DrDamnit

ASKER

here's the catch... t2.type = NULL flat won't exist because the payment hasn't been made yet.

...or am I making it too hard?
If it doesn't exist it will be Null (may need more coffee to think about this)
that gave me 2,390 rows, which is about half of what it should be, and we just found an example invoice number that should have been in the report, but wasn't...
ASKER CERTIFIED SOLUTION
Avatar of Gary
Gary
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Coffee is wonderful.
Uh-Oh.... it's not right!

User generated image
...FYI type3 = invoiced and type4 = paid in real life. I said type 1 and type 2 in the original question for simplicity.
In the screenshot above (to clarify): the first record that we see, invoice # 4258008-1, should not appear in our results query BECAUSE it has both types (in the second image on the screenshot).

Thoughts?
I can't see how you can get two rows with the same ID - the query precludes it.
Is it the same result for both queries?
FYI:
An alternative approach is to use NOT EXISTS

select
*
from your_table
where NOT EXISTS (
      select null
      from your_table as paid
      where `FILE TYPE` = 'type4'
      and your_table.`INVOICE NUMBER` = PAID.`INVOICE NUMBER`
     )
and `FILE TYPE` = 'type3';

{+edit, sorry forgot the last line of the query above, which is necessary}