Hi,
I could not understand why I was getting 6 rows back, when I should
only
have been getting one back, until I realised that I had given an alias
for the table 'fund_class' without using it in the first case. If I use
the alias I get the expected result. Perhaps this should raise an error,
but I think the two queries should not give a different results. This is
with postgres 7.0beta5 on Dec-Alpha.
select f.fc_id,it.el_id,ip.ip_id,m.c_id,m.ip_id
from ip_categories cat, ip_cat_items it, ip_cat_map m, ip_item ip,
fund_class f
where cat.cat_table='fund_class' and cat.cat_id=it.cat_id and
it.el_id=fund_class.fc_id and m.c_id=it.c_id and m.ip_id=ip.ip_id;
fc_id | el_id | ip_id | c_id | ip_id
-------+-------+-------+------+-------
2 | 6 | 6 | 9 | 6
3 | 6 | 6 | 9 | 6
5 | 6 | 6 | 9 | 6
4 | 6 | 6 | 9 | 6
7 | 6 | 6 | 9 | 6
6 | 6 | 6 | 9 | 6
(6 rows)
select f.fc_id,it.el_id,ip.ip_id,m.c_id,m.ip_id
from ip_categories cat, ip_cat_items it, ip_cat_map m, ip_item ip,
fund_class f
where cat.cat_table='fund_class' and cat.cat_id=it.cat_id and
it.el_id=f.fc_id and m.c_id=it.c_id and m.ip_id=ip.ip_id;
fc_id | el_id | ip_id | c_id | ip_id
-------+-------+-------+------+-------
6 | 6 | 6 | 9 | 6
(1 row)
Adriaan