MySQL Left Join Syntax to find missing records?
Posted on 2014-12-08
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?