troubleshooting Question

SQL syntax help with multiple tables

Avatar of mvidas
mvidasFlag for United States of America asked on
FoxProSQL
21 Comments3 Solutions665 ViewsLast Modified:
Hi Everyone,

I'm querying a foxpro database, and don't know nearly enough about sql to get what I want.

I have a few things I want to do with this, but I'm going to break this up into multiple questions.  This is question 1.

I have 2 tables: bills and notes. bills is the main table of a custom billing system we use, notes contains any notes added to an invoice.  

Here is what I have currently:

SELECT bills.billcode, bills.balance, bills.customer, notes.note, notes.noteno
FROM bills, notes
WHERE notes.billno = bills.billno AND bills.balance>$0 AND bills.odate<={d '2007-04-03'}
ORDER BY bills.idate, notes.noteno

I don't really want notes.noteno, I just included it here to show it's existence. What is happening with this query is it is returning too much; ie. if billcode A12345 has 6 notes on it, the records being returned look like:
BILLCODEA     BALANCEA     CUSTOMERA     Note1Text     660
BILLCODEA     BALANCEA     CUSTOMERA     Note2Text     3509
BILLCODEA     BALANCEA     CUSTOMERA     Note3Text     3518
BILLCODEA     BALANCEA     CUSTOMERA     Note4Text     3555
BILLCODEA     BALANCEA     CUSTOMERA     Note5Text     3795
BILLCODEA     BALANCEA     CUSTOMERA     Note6Text     3799
BILLCODEB     BALANCEB     CUSTOMERB     Note1Text     1369
BILLCODEC     BALANCEC     CUSTOMERC     Note1Text     1546
BILLCODEC     BALANCEC     CUSTOMERC     Note2Text     1868


What I want is just the most recent note (based on highest noteno for that billno):

BILLCODEA     BALANCEA     CUSTOMERA     Note6Text     3799
BILLCODEB     BALANCEB     CUSTOMERB     Note1Text     1369
BILLCODEC     BALANCEC     CUSTOMERC     Note2Text     1868


How can I do this?
Thanks!
Matt
ASKER CERTIFIED SOLUTION
suhashegde

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 21 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 21 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros