Please help me with a difficult query

[melissa820]

I can't seem to get this query to work.

What I am trying to do is make a list of all the journal records in the 'faculty_journals' table, and then for each journal name, show a count of how many records in the 'faculty_articles' table contain that journal id. (A record in faculty_articles has a corresponding journal_id that is the id number from faculty_journals). For example if there are 2 records in faculty_articles that list journal id of 5, and in faculty_journals #5 is "Journal of SQL", then I want to display "Journal of SQL: 2 articles". Make sense?

The problem with the query below is that it won't list journals that have no articles associated with them. Here is the query I have tried which does not work:

SELECT a.id, a.journal_name, a.weight, COUNT(b.journal_id) as num FROM faculty_journals a LEFT JOIN faculty_articles b ON a.id=b.journal_id GROUP BY b.journal_id ORDER BY a.journal_name;

The tables are as follows:
faculty_journals:

| id | int(11) | | PRI | NULL | auto_increment |
| journal_name | varchar(255) | YES | | NULL | |
| weight | double(4,2) | YES | | NULL | |

faculty_articles

| id | int(11) | | PRI | NULL | auto_increment |
| authors | varchar(255) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| journal_id | int(5) | YES | | NULL | |

[raf]

hmm ...

mysql doesn't handle GROUP BY like ANSI SQL. For instance, with standard SQL, a.id, a.journal_name, a.weight should be part of the group by or an agregation-function.
Maybe your results are caused by mysql's deviating implemantation...

try
SELECT a.id, a.journal_name, a.weight, COUNT(*) as num FROM faculty_journals a LEFT JOIN faculty_articles b ON a.id=b.journal_id GROUP BY a.id, a.journal_name, a.weight ORDER BY a.journal_name;

[melissa820]

I tried out the query you suggested, this one lists all the journals but the 'num' counts are incorrect... instead of displaying a count of articles for that journal, it just displays '1' every time. Here's an example:

| id | journal | weight | num |
+---+------------------------------------------------+--------+-----+
| 1 | 60 Labour Arbitration Cases | 0.00 | 1 |
| 2 | 98 Labour Arbitration Cases | 0.00 | 1 |
| 3 | Academy of Accounting and Finance | 0.00 | 1 |
| 4 | Academy of Marketing Science | 0.00 | 1 |
| 5 | Accounting and Business Review | 0.00 | 1 |


I want the 'num' field to reflect how many journal articles have that id in the journal_articles table.

As far as I knew, I had to group by faculty_articles.journal_id, because these are the totals that I wanted to get? Hmm...

[melissa820]

Thanks to you, I've got something that works:

select a.id, a.journal_name, a.weight, count(b.journal_id) as num from faculty_journals a left join faculty_articles b on a.id=b.journal_id group by a.id, a.journal_name, a.weight order by a.journal_name;

Thank you so much! I would have never thought of that other grouping.

[raf]

glad you got it working.

still strange though... the RDBM apparently will process the OUTER JOIN differently based on the way that you agregate the recordset. it looks like that if you only include fields from one of the tables inside the GROUP BY and agregationfunctions, that the grouping is then performed before the join. where as if you include fields from both tables in the GROUP BY and agregationfunction, then it looks as if the join is performed first and then the grouping is done on the resulting recordset (as would be the expected behaviour).