Thread

Join/table alias bug

Adriaan Joubert <a.joubert@albourne.com> — 2000-04-20T09:12:25Z

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