Member Avatar for Patrickkahuna

hi,
I am having a problem doing a select in MySQL among multiple tables. I have two tables that are virtually the same except that one table is for January and one is for February. I have two columns in each of the tables i want to match on: Account, AmountPaid. I want to develop a query that will tell me which accounts have a $0 amount paid in both January and in February. I tried:

select count(J.account) from JanTable J, FebTable F where J.AmountPaid=0 and f.AmountPaid=0

but the above query returned a huge number. Anyone have the right approach for this?

Thanks!

  • 2 Contributors
  • 1 Reply
  • 76 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by darkagn
Member Avatar for darkagn

I think in this situation you are better off explicitly joining the tables by account id. Like so:

SELECT COUNT(J.account) from JanTable J
INNER JOIN FebTable F 
ON J.account = F.account
WHERE J.AmountPaid=0 AND F.AmountPaid=0

Otherwise you will get many duplicate records.

commented: agreed +19
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.