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!...

[Fumigator]

Do a simple JOIN and ORDER BY the timestamp descending. Then just fetch the first row, or, you could also include LIMIT 1 in the query.

[ShootingBlanks]

Do a simple JOIN and ORDER BY the timestamp descending. Then just fetch the first row, or, you could also include LIMIT 1 in the query.

Two problems:

1) I can't do an "ORDER BY" on the timestamp because I need to first order by other criteria.

2) I can't do a simple JOIN, simply because I'm horrible at JOINs, so that's why I was on here looking for help. ;)

Any other ideas? Thanks!!!

[Fumigator]

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....

This is what I was going by. Reading it again though, I see your point-- you are fetching more than one proj_id in the query.

OK First let's get you sorted out on what a JOIN is, because you think it's crazy complicated when it really isn't.

You have two tables that share a common column-- proj_id. Rows in the progress table are related to rows in the main table using proj_id. Forget for a moment that there are several flavors of joins (inner, left, right) and zone in on the common join (also called an inner join). This query returns all rows in one table where there is a match in another table, every time there is a match. So if your progress table contains 5 rows with a value of 1000 in proj_id, and there is 1 row in your main table with a value of 1000 in proj_id, a join will return 5 rows (1 multiplied by 5).


SELECT main.proj_id, prog.updated, prog.progress
FROM main_table as main
JOIN progress_table as prog
ON main.proj_id = prog.proj_id


I told you it was simple ;)

Now to join only one of the 5 rows from progress with the 1 row in main, we'll need to add a subselect to the WHERE clause of the query. The subselect is going to use the progress table again, but we're going to give it a different alias so MySQL looks at it as if it were another table altogether. The table inside the subselect also needs to be connected with the main table by way of proj_id.


SELECT main.proj_id, prog.updated, prog.progress
FROM main_table as main
JOIN progress_table as prog
ON main.proj_id = prog.proj_id
WHERE prog.updated =
(SELECT MAX(updated) FROM progress_table as prog2
WHERE prog2.proj_id = main.proj_id)

[ShootingBlanks]

I haven't tried it yet (i will in a minute), but I just wanted to write quickly to thank you so much for the detailed reply. That actually made perfect sense! :thumbsup:

I'll keep you posted as to if it worked, or if I need to re-think this. Thank again!!!

[ShootingBlanks]

Okay - I'm getting the following error. I googled it, but no results really helped me:

Not unique table/alias: 'update_progress'

Here's my full query, with what you tried to tell me to do as well (and some preceeding code, as it is important to the query). This is probably not as basic as I made my original post sound!:
// '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);

[Fumigator]

First off remember on the subselect you have to assign a different alias name to the same table you are using twice. Otherwise MySQL doesn't know which table you are referring to. Note the AS prog2 below:


AND updated_progress.updated = (SELECT MAX(updated) FROM updated_progress AS prog2 WHERE prog2.proj_id = projects.proj_id)


Second off there is more than one coding style to write a JOIN and you have managed to use both together which you should definitely not do. The "old" way (the only way that was available back in the day) was to list the tables separated by commas like this: FROM table1, table2, table3 and then specify the JOIN condition inside the WHERE clause. I used this method for years and years, but am now a convert to the new style, which is to explicitly use the word "JOIN" along with "ON" to specify the join condition.

Your query is using the old style here (eliminating the PHP code):


FROM projects, users, proj_user_lookup
WHERE projects.proj_id = proj_user_lookup.proj_id
AND users.user_id = proj_user_lookup.user_id


The new way to express this would be:


FROM projects
JOIN users
ON projects.user_id = users.user_id
JOIN proj_user_lookup
ON projects.proj_id = proj_user_lookup.proj_id


Using this method allows you to easily add the update_progress table:


FROM projects
JOIN users
ON projects.user_id = users.user_id
JOIN proj_user_lookup
ON projects.proj_id = proj_user_lookup.proj_id
JOIN update_progress
ON projects.proj_id = update_progress.proj_id


One last suggestion: When working with a new query, especially one that has any complexity at all to it, you should write and run the query in something like MyPhpAdmin to get it working the way you need, and then convert that working query over to your server language (PHP in your case). That way you separate PHP stuff from MySQL stuff. Just makes life a lot easier.

[ShootingBlanks]

Your query is using the old style here (eliminating the PHP code):


FROM projects, users, proj_user_lookup
WHERE projects.proj_id = proj_user_lookup.proj_id
AND users.user_id = proj_user_lookup.user_id


The new way to express this would be:


FROM projects
JOIN users
ON projects.user_id = users.user_id
JOIN proj_user_lookup
ON projects.proj_id = proj_user_lookup.proj_id


You're the expert, but I think the above is written wrong (and I'm not sure how to correct it properly). In my original (poorly written) example, I'm first doing:

projects.proj_id = proj_user_lookup.proj_id:

which you're doing second, but that should still be fine:

JOIN proj_user_lookup
ON projects.proj_id = proj_user_lookup.proj_id

However, when I do this second:

users.user_id = proj_user_lookup.user_id

you have done this (first), which is different:

JOIN users
ON projects.user_id = users.user_id

You didn't use the "proj_user_lookup" table like I did. You used the "projects" table instead. I tried to alter my original code to re-write it based on your suggestions, and I'm getting the following error:

Unknown column 'projects.user_id' in 'on clause'

Which makes sense because there ISN'T a "user_id" column on the "projects" table. I think you jumbled the code you tried to re-write for me, but I'm not sure how to fix it. Can you help?...

I can't thank you enough for all the help you've already given me. I've learned a bunch, and it's much appreciated!!!

[ShootingBlanks]

Addendum to my above post:
Incidentally, here's the new query that i tried to write (that's getting the error mentioned in my post above):
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
JOIN users
ON projects.user_id = users.user_id
JOIN proj_user_lookup
ON projects.proj_id = proj_user_lookup.proj_id
JOIN update_progress
ON projects.proj_id = update_progress.proj_id "
WHERE update_progress.updated =
(SELECT MAX(updated) FROM update_progress AS prog2 WHERE prog2.proj_id = projects.proj_id)
ORDER BY users.last_name, projects.proj_name

[Fumigator]

Whoops, you're absolutely right, you should join projects with proj_user_lookup first on proj_id, then join proj_user_lookup with users next on user_id.


FROM projects
JOIN proj_user_lookup
ON projects.proj_id = proj_user_lookup.proj_id
JOIN users
ON proj_user_lookup.user_id = users.user_id
JOIN update_progress
ON projects.proj_id = update_progress.proj_id

[ShootingBlanks]

Whoops, you're absolutely right

Wow - I really WAS paying attention! ;)

Everything works now. Seriously - you were a HUUUGE help. Thanks so much for not only giving me the solution, but for EXPLAINING it so that I can actually gain some knowledge in the process. Thanks SO much!!!

[Fumigator]

You're welcome :)