DrDamnit
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?
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?
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?
...or am I making it too hard?
If it doesn't exist it will be Null (may need more coffee to think about this)
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Coffee is wonderful.
ASKER
...FYI type3 = invoiced and type4 = paid in real life. I said type 1 and type 2 in the original question for simplicity.
ASKER
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?
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?
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}
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}
Open in new window