CODEWITHSUNDEEP
mysqlsqljoin
Burning Hippo
Burning Hippo

Reputation: 805

duplicate records in SQL JOIN

I am getting multiple results in my SQL query. I have three tables: payments, credits, and charges. At the end of each month I need to have a balance forward based on the account holder (let's call said person owner) I have tried different queries but keep getting duplicate rows. I know it has to do with my WHERE clause but I don't know how to fix it.

In my test data I have one record for payments:

owner: 1001 date:2014-03-23 amount:50.00

I get 3 of that record returned from the query:

SELECT p.amount
FROM payments p, charges ch, credits cr
WHERE (p.owner = 1001 AND ch.owner = 1001 AND cr.owner = 1001)

I know that isn't right, but I do not know the proper way to query it. I want just the one row returned. Eventually I will use a SUM() on the amount from each table and add a dates to the WHERE clause.

First, though, I need to get this resolved. Thanks for any help.

EDIT: I have made a SQL Fiddle of the tables. The names are less generic. I am actually using unit, not owner. Though it really wouldn't matter.

Upvotes: 2

Views: 223

Answers (2)

Paresh Gami
Paresh Gami

Reputation: 4792

Try this one

SELECT p.amount
FROM payments p, charges ch, credits cr
WHERE (p.owner = 1001 AND ch.owner = 1001 AND cr.owner = 1001 AND p.owner=ch.owner AND
p.owner=cr.owner
)

Upvotes: 1

kimbarcelona
kimbarcelona

Reputation: 1136

You can limit it with

SELECT p.amount FROM payments p, charges ch, credits cr WHERE (p.owner = 1001 AND ch.owner = 1001 AND cr.owner = 1001) LIMIT 1

or in any case, one of those three tables has three record for owner 1001 that's why it's displaying three. It's better if you can give the data of 1001 from these three tables so we can see

Upvotes: 0

Related Questions