?
Solved

MySQL Left Join Syntax to find missing records?

Posted on 2014-12-08
11
Medium Priority
?
396 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, we’ll look at how to deploy ProxySQL.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 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