Solved

MySQL Left Join Syntax to find missing records?

Posted on 2014-12-08
11
375 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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 48

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

749 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