SELECT query is returning duplicates records

#1

Hello Friends,

I have this query, which select fields from 2 tables

SELECT D.debtor_id,D.fname,D.lname,M.lastDate,M.WeeksDue FROM debtor_information D,debtor_money M WHERE D.creditor_id='457456' AND M.creditor_id='457456';

you can see a demo here and hopefully contribute to this fiddle. http://sqlfiddle.com/#!2/c264a/2

I want only 1 debtor_id for each debtor.

#2

Add this to the end of your query

[php]GROUP BY D.debtor_id[/php]

[php]SELECT D.debtor_id,D.fname,D.lname,M.lastDate,M.WeeksDue
FROM debtor_information D,debtor_money M
WHERE D.creditor_id=‘457456’
AND M.creditor_id=‘457456’
GROUP BY D.debtor_id;[/php]

#3

Worked perfect !!!.

Im confused now. How group by eliminated the duplicates? does it convert all duplicates to just 1 ? or merge?

#4

it did only show one unique record however the fields from the second table DO NOT match the first table records

#5

Why do you only want back one row? Some debtor ID have multiple values for the M.lastDate and M.WeeksDue.

If you eliminate those two fields it will be easy to pull back unique records, or if you want the max of those two fields…

#6

Then your query or your data is not right.

#7

I only need the unique values from the 2 tables no dupes. Every debtor_id might or might not have more than 1 corresponding records from the second table. In this case there are 2 debtors who has more than 1 records on the second table. Debtor_id 1 and 38

#8

Might!!. who knows. I can post here the whole create sql if the sql fiddle is not enough. Just let me know

#9

In the case of 1 and 32, do you want multiple rows for that?

#10

38*. In thiz case yes but it can be different tomorrow. For example debtor_id 10 who only have 1 record aka loan tomorrow might get another loan on top of the one he already has so now he has 2 records on the second table.

#11

Anyway, I have a feeling this produces the results you’re looking for…

[php]SELECT D.debtor_id,D.fname,D.lname,M.lastDate,M.WeeksDue
FROM debtor_information D, debtor_money M
WHERE D.creditor_id= M.creditor_id and D.debtor_id = m.debtor_id
AND M.creditor_id=‘457456’[/php]

#12

Yep it does exactly what im looking for.
also i found another way of doing it and just wanted to share it with you guys

SELECT D.debtor_id,D.fname,D.lname,M.lastDate,M.WeeksDue FROM debtor_information D join debtor_money M on (D.debtor_id=M.debtor_id) WHERE D.creditor_id='457456';

OR

SELECT D.debtor_id,D.fname,D.lname,M.lastDate,M.WeeksDue FROM debtor_information D, debtor_money M WHERE D.creditor_id= M.creditor_id and D.debtor_id = m.debtor_id AND M.creditor_id='457456'

both queries fetch the same result. however which one might run faster? or which one you recommend?

#13

They are both the same, no speed difference.