Difficult JOIN

bpgillett

hi. i'm displaying users' names in an html table ordered by the number of evaluations they have submited. my table displays: user id, number of evals, and date of last eval. all users' id's are in a table called 'users'; however, only users who have submitted evaluations have their id's in the evaluations table. i can succesfully count and order the id's from the evaluations table, but i want the id's that are abscent from the 'evaluations' table (but present in 'users' table) to display with a count equal to 0. i can't figure out how to join the two tables without screwing up the count. thanks. --brian

//get the users' id (a_id), respective number of evals and date of last eval from evaluations table; order them in descending order by number of evaluations.
$result = mysql_query("SELECT a_id, COUNT(eval_id) as eval_num, MAX(date) AS last_eval FROM evaluations GROUP BY a_id ORDER BY eval_num DESC",$db);
if ($myrow = mysql_fetch_array($result)){

//start html table
echo "<table border=1>\n";
echo "<tr><td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\"><strong>Attending</strong></font></td>";
echo "<td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\"><strong>Evals</strong></font></td>";
echo "<td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\"><strong>Last Eval</strong></font></td>";

do {

//output to html table
printf("<tr><td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">%s</font></td>", $myrow["a_id"]);
printf("<td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">%s</font></td>", $myrow["eval_num"]);
printf("<td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">%s</font></td>", $myrow["last_eval"]);

} while ($myrow = mysql_fetch_array($result));

//end table
echo "</tr></table>\n";
}

trexx

you could do a "left outer join" on the users table. that will give you all rows that are in the users table and, if there's no matching id in the evaluations table, a null value otherwise the row from the evaluations table. In the where clause you filter out records that are in both the user and the evaluation table, leaving only the records that are only in the user table but not in the evaluation table.

select *
from users u
left outer join evaluations e on u.a_id=e.a_id
where e.a_id is null

Roger_Ramjet

trexx wrote:
you could do a "left outer join" on the users table. that will give you all rows that are in the users table and, if there's no matching id in the evaluations table, a null value otherwise the row from the evaluations table. In the where clause you filter out records that are in both the user and the evaluation table, leaving only the records that are only in the user table but not in the evaluation table.
select *
from users u
left outer join evaluations e on u.a_id=e.a_id
where e.a_id is null

Good Trexx, except that bpgillet does not need the WHERE clause. Just test for NULL when formatting the output and substitute zero instead.

bpgillett

great! thanks. both suggestions work. can both queries be combined into one? this way the users in which e.a_id is null can still be ordered by name with the other users. (i offer the option to order by eval_num or last_name)

SELECT a_id, COUNT(eval_id) as eval_num, MAX(date) AS last_eval FROM evaluations GROUP BY a_id ORDER BY eval_num DESC

SELECT u.first_name, u.last_name FROM users u LEFT OUTER JOIN evaluations e ON u.a_id=e.a_id WHERE e.a_id is null

currently, i just use the latter query to display these names at the bottom of my html table. looks good when ordered by eval_num, but problematic when ordering by last_name.

thanks. --brian

Roger_Ramjet

Yes, if you omit the WHERE clause then you will get all users and those without any eval will have NULL in the columns. Just test for null and substitute zero or whatever when displaying the results.

trexx

Yes, you can do all in one query:

select u.a_id, u.first_name, u.last_name, sum(if(e.a_id is null, 0, 1)) eval_num, max(e.date)
from users u left outer join evaluations e
on u.a_id=e.a_id
group by u.a_id, u.first_name, u.last_name
order by eval_num

bpgillett

thanks. trexx, your code works perfectly. it's a great learning example for me also.

--brian