Doing Joins with MySQL

[Arc]

Hello. I need to get distinct dates from 3 different tables.

My SQL is like this (I know it's not right, just giving an idea of what I am trying to do). DateAdded is a common field in all three tables and I get this error when trying this Column: 'DateAdded' in field list is ambiguous

$Sql="SELECT DISTINCT DateAdded,DATE_FORMAT(DateAdded,'%Y-%b-%d') AS ShowDate FROM tblJobCost,tblJobBudget,tblJobBudgetChanges ORDER BY DateAdded DESC";

So I need to pull distinct dates from those 3 tables tblJobCost,tblJobBudget,tblJobBudgetChanges with no duplicates. Is this possible with MySQL?

Thanks!😃

[Pig]

It is ambigous because you have 3 tables, and only one field. If you are looking for one long list, you need to do 3 different queries. One for each table.

What you are trying to do is this:

SELECT DISTINCT(tblJobCost.DateAdded) AS ShowDate1, DISTINCT(tblJobBudget.DateAdded) AS ShowDate2...

But this will give you 3 columns of data, not one. I don't believe you can use the same name (ie: ShowDate) for each one, though you could try and see what it does. AFAIK, that's not legal.

I would just one run query for each table, and put them into an array.

$tables = array("tblJobCost", "tblJobBudget", "tblJobBudgetChanges");
foreach($tables AS $value)
{
     $result = mysql_query("SELECT DateAdded FROM $value");
     while( $row = mysql_fetch_array($result) )
     {
          $date_added[] = $row[0];
     }
}
sort($date_added);

[Arc]

Yah that's what I ended up doing. It seems highly ineficient though...🙁

I do like the way you did the table array though. I had it as 3 totaly seperate querys.

Thanks!😃

[Lars_Berg]

select DATE_FORMAT(DateAdded,'%Y-%b-%d') AS ShowDate 
FROM tblJobCost
union
select 
DATE_FORMAT(DateAdded,'%Y-%b-%d') 
from tblJobBudget
union 
select DATE_FORMAT(DateAdded,'%Y-%b-%d') 
from tblJobBudgetChanges 
ORDER BY ShowDate DESC