Joining tables with identical fields

[BigTime]

Hi there.

Having trouble with a join, which then goes into a loop.  Both tables have an identical structure.  Im trying to join everything, then spit out my normal loop with just the records across both tables that have a similar entry in fieldname.....then sort all those results, by day, and time...the loop just helps me seperate the records by week, with each week getting their own table....and Im passing the fieldname variable via the url ie mypage.php?fieldname=ThisField

My problem is that they have the same column names, so I keep getting Column: 'time' in field list is ambiguous...Im unsure how to solve it as Im formating time when its coming out, but I need all the records to sort on that column across both tables.

[code]<?
    # setup SQL statement
$SQL = " SELECT time, league, week, hometeam, awayteam, month, date, division, id, fieldlink, fieldname, TIME_FORMAT(time, '%l.%i %p') AS newtime FROM pac10 JOIN mac10 ON ( pac10.fieldname = mac10.fieldname) WHERE fieldname LIKE '$fieldname%' ORDER BY week ASC, date ASC, fieldname ASC, time ASC";

    # execute SQL statement
    $retid = mysql_db_query($db, $SQL, $cid);

    # check for errors
    if (!$retid) { echo( mysql_error()); }

    else {
    $weektracker = 0;
while ($row = mysql_fetch_array($retid)) {
$league = $row["league"];
$week = $row["week"];
$hometeam = $row["hometeam"];
$awayteam = $row["awayteam"];
$month = $row["month"];
$date = $row["date"];
$newtime = $row["newtime"];
$homescore = $row["homescore"];
$awayscore = $row["awayscore"];
$id = $row["id"];
$division = $row["division"];
$homeconference = $row["homeconference"];
$awayconference = $row["awayconference"];
$fieldlink = $row["fieldlink"];
$fieldname = $row["fieldname"];

#BEGINNING MY LOOP BY WEEK AND DATA OUTPUT

{
  if($weektracker < $week)
  {
      if($weektracker > 0)
  echo ("</table><BR><BR>\n");

      echo ("<b><font face=arial size=2 color=#e0e0e0>WEEK $week</b></font>\n");
      echo ("<TABLE cellpadding=2 border=1 width=99% style=\"border-collapse: collapse; border: solid; border:1px;\">");
  echo ("<TR><TD><font face=arial size=1 color=#e0e0e0><b>LEAGUE</b></td><TD width=80><font face=arial size=1 color=#e0e0e0><b>DIVISION</b></TD><TD width=40><font face=arial size=1 color=#e0e0e0><B>DATE</B></TD><td width=60><font face=arial size=1 color=#e0e0e0><b>TIME</b></TD><td><font face=arial size=1 color=#e0e0e0><B>AWAY</B></TD><td><font face=arial size=1 color=#e0e0e0><b>HOME</b></td><TD><font face=arial size=1 color=#e0e0e0><b><center>FIELD</center></b></TD></TR>");
      $weektracker = $week;
    }


            echo ("<TR>");
            echo ("<TD><font face=arial size=1 color=#e0e0e0>$league</font></td><td><font face=arial size=1 color=#e0e0e0>$division</td><td><font face=arial size=1 color=#e0e0e0>$month - $date</td><td><font face=arial size=1 color=#e0e0e0>$newtime</TD><TD><font face=arial size=1 color=#e0e0e0>$awayteam</td><td><font face=arial size=1 color=#e0e0e0>$hometeam</td><td><a href=\"http://$fieldlink\" target=_blank><font face=arial size=1 color=#e0e0e0>$fieldname</td>\n");
echo ("</TR>");
  }}

        echo ("</TABLE><BR>");



}

   

?>[/code]

thanks in advance for any insight :)

[fenway]

You need to use column and table aliases.

[BigTime]

Thanks Fenway....these boards used to be quite helpful with directives, now its all like generic canned spam.

For anyone finding this in a search....this is what I have learned:

JOINS are used for adding vertical colums...for instance having 2 vertical labeled colums from one table and then 5 other labeled columns from another table which would create a 7 columned table.

UNIONS are used for adding horizontal rows...so this is what I was after rather than a JOIN.

UNIONS need the fields aliased if you want to ORDER them....so here is my final query that worked:

[code]$SQL = "SELECT time as t, league, week as w, hometeam, awayteam, month, date as d, division, fieldlink, fieldname as f, TIME_FORMAT(time, '%l.%i %p') AS newtime FROM pac10 WHERE fieldname LIKE '$fieldname%'
UNION
SELECT time as t, league, week as w, hometeam, awayteam, month, date as d, division, fieldlink, fieldname as f, TIME_FORMAT(time, '%l.%i %p') AS newtime FROM mac10 WHERE fieldname LIKE '$fieldname%'
UNION
SELECT time as t, league, week as w, hometeam, awayteam, month, date as d, division, fieldlink, fieldname as f, TIME_FORMAT(time, '%l.%i %p') AS newtime FROM big10 WHERE fieldname LIKE '$fieldname%'
ORDER BY w ASC, d ASC, f ASC, t ASC"; [/code]