How to deal with mySQL JOINS with identical column names in two or more tables? - 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: How to deal with mySQL JOINS with identical column names in two or more tables? (/showthread.php?tid=2296) |
How to deal with mySQL JOINS with identical column names in two or more tables? - El Forum - 07-27-2007 [eluser]smalljohnson[/eluser] Hi, I have a mySQL question. I am trying to perform a LEFT JOIN on two tables. The two tables have a couple of identically named columns (CreatedDate and ModifiedDate). When I perform a LEFT JOIN and print_r the results, any of the non-unique columns are shown only once (shows the value from the table to the left of the JOIN clause - the Members.CreatedDate and Members.ModifiedDate in this case). Is there a way to have the query return the CreatedDate and ModifiedDate from both tables without giving them unique names in the table? I know that I could use 'AS' but that would get very tedious as I have about 20 different tables in my db with several having idential column names. Code: CREATE TABLE `Members` ( How to deal with mySQL JOINS with identical column names in two or more tables? - El Forum - 07-27-2007 [eluser]sikkle[/eluser] Quote:Is there a way to have the query return the CreatedDate and ModifiedDate from both tables without giving them unique names in the table? I know that I could use ‘AS’ but that would get very tedious as I have about 20 different tables in my db with several having idential column names. You have to read about the alias. (AS) After taking time to do this, you'll find your query easy to read. How to deal with mySQL JOINS with identical column names in two or more tables? - El Forum - 07-28-2007 [eluser]smalljohnson[/eluser] Thanks for the reply. I am able to display the modified and created dates from both table using the following select statement: Code: $sql = " But this seems like it will get quite long if I end up joining 2 more tables (total of 4) that all have the same CreatedDate and ModifiedDate fields. Plus, some of the tables have additional column names that are have the same spelling but different values - which would require additional aliases in the SELECT statement. Is there a better way of doing this? How to deal with mySQL JOINS with identical column names in two or more tables? - El Forum - 07-28-2007 [eluser]Rick Jolly[/eluser] Maybe this will shorten your query even more: Code: $sql = " How to deal with mySQL JOINS with identical column names in two or more tables? - El Forum - 07-29-2007 [eluser]Phil Sturgeon[/eluser] Also to make things easier, look up join() in the Active Record part of the usermanual. |