As per Tony891206 saying, you'Re doing a cartesian product. To avoid it, you need to tell how to join both tables together.
d.custid = c.custid
As follows:
select a.acno,c.fname,c.lname,b.bname,a.acbal
from branch_details b,
custac a,
custacdetails d,
custdetails c
where d.custid = c.custid
and c.custid=11111
and a.acno=d.acno
and a.branchid=b.bid
and actype='SA';
All credit goes to Tony891206.
This being said, as stated by a_horse_with_no_name:
Another good example why using explicit JOIN operators is better than the (outdated) implicit join in the where clause: you can't forget the join condition.
So the correct query should be written as follows.
select a.acno, c.fname, c.lname, b.bname, a.acbal
from branch_details b
join custac a on a.bid = b.bid
join custacdetails d on d.acno = a.acno
join custdetails c on c.custid = d.custid
where c.custid = 11111
and d.actype = 'SA'
This last query makes the relationship between the tables glaringly obvious, and allows for room in the where clause to specify only filter criterion.
If you want to know more about SQL syntaxes, please visit the followings:
In short, you would have had better chances to solve your problem by yourself using the SQL-92 join syntax over the SQL-89 one, since you would have had no other choice than to ask yourself how your tables may get joined together while writing the join clauses.
In addition to it, some say that there is a slight improvement on the performance using SQL-92 syntax, which I personally doubt. Besides, I do evangelize SQL-92 join syntax for it is easier to read than the SQL-89.
