Solved

MySQL Left Join Syntax to find missing records?

Posted on 2014-12-08
11
384 Views
Last Modified: 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?
0
Comment
Question by:DrDamnit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40487240
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

0
 
LVL 32

Author Comment

by:DrDamnit
ID: 40487256
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?
0
 
LVL 58

Expert Comment

by:Gary
ID: 40487265
If it doesn't exist it will be Null (may need more coffee to think about this)
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 32

Author Comment

by:DrDamnit
ID: 40487304
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...
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40487324
Coffee does wonders

If you only want the invoice number
SELECT invoice_number
FROM table
GROUP BY invoice_number
HAVING count(invoice_number) = 1

Open in new window


If you want more details
SELECT *
FROM table
WHERE invoice_number IN (
SELECT invoice_number
FROM table
GROUP BY invoice_number
HAVING count(invoice_number) = 1
)

Open in new window

0
 
LVL 32

Author Closing Comment

by:DrDamnit
ID: 40487571
Coffee is wonderful.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 40487631
Uh-Oh.... it's not right!

query fail.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 40487632
...FYI type3 = invoiced and type4 = paid in real life. I said type 1 and type 2 in the original question for simplicity.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 40487637
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?
0
 
LVL 58

Expert Comment

by:Gary
ID: 40487646
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?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40487935
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}
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question