Hello I have the following query And i’m getting duplicate for some record.
SELECT DISTINCT(D.debtor_id),D.fname,D.lname,M.lastDate FROM debtor_information D join debtor_money M on (D.debtor_id=M.debtor_id)
WHERE D.creditor_id='457456' AND D.debtor_id NOT IN (SELECT `debtor_id` FROM debtor_money WHERE `balance` > 0)
please view my SQL Fiddle
in the fiddle you can Gilson Ramos show Twice.
my first thought was to use DISTINCT but is not working and I’m outa ideas.
In simple terms, tell me what you want to know about your data.
I have 2 tables, debtor_information and debtor_money. This is an one to many relationship debtor_money having multiple records related to 1 record in debtor_information.
What I want I actually accomplish it with the following query. just that i don’t want dupe.
SELECT DISTINCT(D.debtor_id),D.fname,D.lname,M.lastDate FROM debtor_information D join debtor_money M on (D.debtor_id=M.debtor_id)
WHERE D.creditor_id='457456' AND D.debtor_id NOT IN (SELECT `debtor_id` FROM debtor_money WHERE `balance` > 0)
In plain words what I want to retrieve from the database is debtors on both table (It’s possible that a debtor don;t have related data on the second table ) that is why i used
join debtor_money M on (D.debtor_id=M.debtor_id)
AND D.debtor_id NOT IN (SELECTdebtor_idFROM debtor_money WHEREbalance> 0)
Again in plain words:
I want debtors on both tables and debtors who has no records with balance on debtor_money.
Add the following to the end of your query
GROUP BY D.debtor_id
Well Kevin Rubio, YOU THE MAN!!!
The solution was very simple but when you over look and stress too much at something you get brain-fail.
Glad to help. Feel free to give a brother some karma