Solved

MySQL Left Join Syntax to find missing records?

Posted on 2014-12-08
11
361 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now