UNION question...

pointsplat

For all those keeping track up in general, yes this is about the same thing I was asking up there but now with new issues so I decided to start here.

According to MySQL.com you cannot use UNION in version 3.x so you have to go the ghetto way about it and use left joins and a dummy table, blah blah blah. All outlined here http://jinxidoru.com/tutorials/union.html can anyone assit me in getting this to work....what I need to do is select all of the teachers table as well as select the number of comments (tabulated by counting the rows of comment_ids that have the same professor ID). and sort them in order by number of comments. The problem with this is without the union I am only going to return the values that have comments. Using UNION you can all in the same SQL query return the values with and without comments. However since I dont have MySQL 4.0 this is my option below (not working) can anyone seeing any glaring problems?

SELECT teachers. * , count( teachers_comments.comment_id ) 
as number_comments
FROM union_table_keep AS D
LEFT JOIN teachers ON ( D.num = 0 ) 
LEFT JOIN teachers_comments ON ( D.num = 1 AND teachers.prof_id = teachers_comments.prof_id ) 
WHERE D.num < 2
GROUP BY teachers_comments.prof_id
ORDER BY number_comments

steadyguy

Assuming two simple tables, teachers with columns id and name, and comments with columns id, comment, and teacher ...

SELECT t.id, t.name, COUNT(comments.comment) AS commentCount
FROM teachers AS t
LEFT JOIN comments
ON t.name = comments.teacher
GROUP BY t.id
ORDER BY commentCount DESC;

This query will yield a result set with columns id, name, and commentCount. The results will be have the teachers with the most comments at the top, and those with none at the bottom.

Is this the sort of thing you were looking for?

pointsplat

YOU ARE THE F'IN MAN!!!

Next term I am definately taking a database course to learn more about this stuff!! For now a couple name changes and the code works PEEEEEERRRRFECT!!

Thank ya for the help.

steadyguy

No worries ...🆒