How to deal with mySQL JOINS with identical column names in two or more tables?

[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` (
  `MemberID' INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `FirstName` VARCHAR( 100 ) NOT NULL ,
  `LastName` VARCHAR( 100 ) NOT NULL ,
  `CreatedDate` timestamp(14) NOT NULL,
  `ModifiedDate` timestamp(14) NOT NULL
  );

  CREATE TABLE `Addresses` (
  `AddressID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `MemberID_fk` INT NOT NULL default '0',
  `AddressType` VARCHAR( 20 ) NOT NULL ,
  `Address` VARCHAR( 100 ) NOT NULL ,
  `City` VARCHAR( 100 ) NOT NULL ,
  `State` VARCHAR( 100 ) NOT NULL ,
  `CreatedDate` timestamp(14) NOT NULL,
  `ModifiedDate` timestamp(14) NOT NULL
  );


$sql = "SELECT *
        From Members
        LEFT JOIN Addresses ON Addresses.MemberID_fk = Members.MemberID
       ";

$query = $this->db->query($sql)

foreach($query->result() as $row):
      print_r($row);
      echo "<br>";
endforeach;

[eluser]sikkle[/eluser]

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.

[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 = "
        SELECT Members.*,
               Addresses.CreatedDate AS cdate,
               Addresses.ModifiedDate AS mdate,
               Addresses.*
        From Members
        LEFT JOIN Addresses ON Addresses.MemberID_fk = Members.MemberID
        ";

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?

[eluser]Rick Jolly[/eluser]
Maybe this will shorten your query even more:

Code:

$sql = "
        SELECT m.*,
               a.CreatedDate AS cdate,
               a.ModifiedDate AS mdate,
               a.*
        From Members AS m
        LEFT JOIN Addresses AS a ON a.MemberID_fk = m.MemberID
        ";

[eluser]Phil Sturgeon[/eluser]
Also to make things easier, look up join() in the Active Record part of the usermanual.