db join gives 'not unique alias' error [SOLVED]

db join gives 'not unique alias' error [SOLVED] - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: db join gives 'not unique alias' error [SOLVED] (/showthread.php?tid=23553)

db join gives 'not unique alias' error [SOLVED] - El Forum - 10-14-2009

[eluser]nilsm[/eluser]
Hello,

when I do a join without assigning aliases to the table names, I get a SQL error:

Here is my CI code:

Code:
$this->db->select('user_sections.*, section_types.name AS sectionType', FALSE);

$this->db->from('user_sections');

$this->db->join('section_types', 'user_sections.typeid = section_types.id');

and here is the SQL error. See how it doubles up on table names in the 'from' part of the query? That is what is causing the error and I'm not sure why it's happening:

Code:
Not unique table/alias: 'user_sections'

SELECT user_sections.*, section_types.name AS sectionType FROM (user_sections, user_sections) JOIN section_types ON user_sections.typeid = section_types.id

I can workaround by assigning aliases to the table names in the 'from' and 'join' sections but it seems like a CI bug or perhaps a misunderstanding on my part, so was hoping there was another fix.

This code works:

Code:
$this->db->select('a.*, b.name AS sectionType', FALSE);

$this->db->from('user_sections as a');

$this->db->join('section_types as b', 'a.typeid = b.id');

Thanks - Nils

db join gives 'not unique alias' error [SOLVED] - El Forum - 10-14-2009

[eluser]nilsm[/eluser]
I also just realised that even with aliases, the CI-generated query still doubles up on table names in the 'FROM' block. The example above generates this SQL:

Code:
FROM (user_sections as a, user_sections)

It doesn't fail anymore because the duplicate is resolved via the alias - but why the double-up in the first place??

db join gives 'not unique alias' error [SOLVED] - El Forum - 10-14-2009

[eluser]nilsm[/eluser]
I'm really sorry but I spotted my mistake - I still had the table name in the final '$this->db->get()' clause! That was causing the double-up in the FROM clause and hence the error messages.