select distinct cust.no, cust.branch, cust.name, cust.surname, term.benefit, term.code,tehi.trcode, tehi.trancode, dmhi.trancode, dmd.benefit
from 
cust as cust inner join term as term on cust.rowno = term.rowno_custterm_cust
inner join tehi as tehi on term.rowno =tehi.rowno_termtehi_term
inner join dmd as dmd on cust.rowno =dmd.rowno_custdmd_cust
inner join dmhi as dmhi on dmd.rowno =dmhi.rowno_dmddmhi_dmd
group by cust.no, cust.branch, cust.name, cust.surname, term.benefit, term.code,tehi.trcode, tehi.trancode, dmhi.trancode, dmd.benefit
having count(*) > 1

this doesn't even work..
if i delete the last row having count(*)>1 then it gives me duplicate values.
Please Advice how would i write this query.

THANKS

I have a similar problem a few days ago, the problem was that i forgot to link a primary key with a foreign key, i suggest you that double check your links and that your not forgetting anything.

I do have foreign and primary keys

yes, but what i mean is make sure you are linking everything, for example i see you are linking the rowno field between all tables but maybe there is one table that you have to match two fields the rowno and another one to make that row distinct.

I know what you are saying. I have checked it, they are linking correct. Is there another way to write it, maybe using sub queries?

Well in order for me create a query i would need you post the design table in what exactly you are expecting as a result. then i can help you.

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.