Help writing a MySQL query?...

[ShootingBlanks]

Hello.  I'm not very good with joins and things, so I was wondering if someone could help me (I'm still new at MySQL)...

 

Basically I have two tables.  One has a bunch of information in it, and the unique primary key is an auto-incremented column called "proj_id".

 

The second table has no unique ids.  It has three columns - "proj_id", "updated', and "progress".  The "proj_id" column will have values from the first table, but they can be repeated (so, for example, there would be only one "13" in the first table, but there could potentially be any number of "13"s in the second table).  The "updated" column in this second table is a timestamp column, so I guess that would actually be unique, since it's the current time the row is added...

 

What I need is a query that will get me everything from the first table, and then only the most recent "proj_id" entry (that whole row) for each ID from the first table that appears in the second table....

 

So, if the second table has three listings with a "proj_id" of "13", then I'd only want the row that had the most recent date in the "timestamp" column of that row...

 

I hope that made sense.  Any help is greatly appreciated.  Thanks!...

 

[fenway]

You need to do this in two steps:

 

1) Write the query that will get the most project id based on update.

2) join that table back to the first table.

[ShootingBlanks]

Sorry if what you're about to read is super confusing, but here's what I have so far.  I'm getting the following error on it:

 

Not unique table/alias:

 

// 'WHERE' CODE FOR SQL IF USER IS SELECTED TO FILTER
$whereSQL = "WHERE projects.proj_id = proj_user_lookup.proj_id ";
$whereSQL .= "AND users.user_id = proj_user_lookup.user_id ";
if (isset($_GET['user_id']) && $_GET['user_id'] != "all") {
$whereSQL .= "AND users.user_id=".$_GET['user_id']." ";
}
if (isset($_GET['openClosed']) && $_GET['openClosed'] != "all") {
$whereSQL .= "AND projects.status='".$_GET['openClosed']."' ";
} else if (!isset($_GET['openClosed'])) {
$whereSQL .= "AND projects.status='Open' ";
}
$whereSQL .= "AND projects.due >= '$date1' AND projects.due <= '$date2' ";
$whereSQL .= "AND updated_progress.updated = (SELECT MAX(updated) FROM updated_progress WHERE updated_progress.proj_id = projects.proj_id)";

// START QUERY
mysql_select_db($database_ProjectBoard, $ProjectBoard);
$query_getData = "SELECT projects.proj_id, projects.proj_name, projects.sponsor,
			  projects.priority,projects.division, projects.due, projects.progress,
			  projects.status,users.last_name, users.user_id, proj_user_lookup.proj_id,
			  proj_user_lookup.user_id,
			  CONCAT(DATE_FORMAT(update_progress.updated, '%Y-%m-%d'),': ',update_progress.progress) AS progress2,
				DATE_FORMAT(update_progress.updated, '%Y-%m-%d') AS updated_date 
			  FROM projects, users, proj_user_lookup, update_progress 
				JOIN update_progress ON projects.proj_id = update_progress.proj_id "
			  .$whereSQL."
			  AND projects.proj_id = update_progress.proj_id 
			  ORDER BY ".$orderBy." ".$orderType;
$query_limit_getData = sprintf("%s LIMIT %d, %d", $query_getData, $startRow_getData, $maxRows_getData);
$getData = mysql_query($query_limit_getData, $ProjectBoard) or die(mysql_error());
$row_getData = mysql_fetch_assoc($getData);

[fenway]

I'm sorry, I can't read that at all... could you echo $query_getData?

[ShootingBlanks]

I'm sorry, I can't read that at all... could you echo $query_getData?

I can't really echo anything, since I'm getting that "Not unique table/alias: 'update_progress'" error.  ???

 

The page won't even display.

 

 

[fenway]

I can't really echo anything, since I'm getting that "Not unique table/alias: 'update_progress'" error.  ???

 

The page won't even display.

Then don't run the query.