Solved

MySQL Left Join Syntax to find missing records?

Posted on 2014-12-08
11
370 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Creating and Managing Databases with phpMyAdmin in cPanel.
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…

773 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