Can this be done without a right join (or with)

[cgraz]

I am writing a blogging system to get better with MySQL and I am trying to grab all the categories, category id's, and the number of articles in each category all in one query. I've almost gotten it with a right join, but of course right joins will return NULL in the fields that don't match up. So I figure I need a different way to tackle this as it seems like it would be a common query. Here's what I've got:

categories table
id
cat_title

articles
id
art_title
summary
body
...etc

posted_in table
id
art_id
cat_id

And here's my SQL

SELECT COUNT(art_id) AS cnt, cat_id, cat_title FROM posted_in RIGHT JOIN categories on categories.id = posted_in.cat_id GROUP BY cat_title

This returns

cnt | cat_id | cat_title
0 | NULL | Broadband
1 | 6 | Business
0 | NULL | CSS
0 | NULL | Disaster Recovery
0 | NULL | PHP
2 | 3 | Security
1 | 7 | VPN's

This is great, except everytime cnt is 0 the cat_id field is NULL. Also if anyone could help me (as a seperate query) how to get the same desired results without NULL, and the id and art_title out of the articles table.

Thanks.

[bastien]

SELECT COUNT(art_id) AS cnt, cat_id, cat_title FROM posted_in RIGHT JOIN categories on categories.id = posted_in.cat_id
where cat_id is not null
GROUP BY cat_title

[cgraz]

Thanks Bastien. I just re-read my post and realized it wasn't clear.

What I meant by I don't want the word NULL to appear is that instead of null, I want it to be replaced with the actual cat_id. I don't have access to the cat_id on the rows that have a cnt of 0. What I want is even if the cnt is zero, I need to have access to the cat_id so I can list things in this format

CATEGORY (5 articles)
CATEGORY 2 (3 articles)
CATEGORY3 (1 article)

and the link for each category would be lets say edit.php?cat_id=7, which is why I need the cat_id. So by eliminating the word NULL, it leaves out the the categories who don't have articles published under them, but if we leave the word null, I then have the names of all the categories, but I don't have the cat_id of the ones with no articles. I hope that makes sense.

If possible, as a separate query (for a different page), I'd also like to list the names of the articles under each category like:

CATEGORY (5 articles)
Article 1
Article 2
Article 3
Article 5
Article 6
CATEGORY 2 (3 articles)
Article 2
Article 4
Article 5
CATEGORY3 (1 article)
Article 3

Thanks for the help, and sorry for the confusion.

[bastien]

try

SELECT 
  COUNT(p.art_id) AS cnt, p.cat_id, c.cat_title 
FROM 
  posted_in p RIGHT OUTER JOIN categories c 
ON 
  categories.id = posted_in.cat_id
WHERE 
  cat_id is not null
GROUP BY 
  cat_title

two

try

SELECT
  c.cat_title, a.art_title
FROM
  posted_in p 
inner join 
  articles a ON p.art_id = a_id
inner join 
  category c  ON p.cat_id = c.id

[cgraz]

Hi Bastien thanks for all your help.

The first query returned only the categories that hard an article count of zero, so it didn't quite work. But right before you posted, I had been playing around and got it to work with this query

SELECT COUNT(posted_in.art_id) AS cnt, 
     categories.id, categories.cat_title from categories 
LEFT JOIN posted_in ON categories.id = posted_in.cat_id 
GROUP BY cat_title

which returns

cnt | id | cat_title
1| 4 | Broadband
1| 6 | Business
1 | 9| CSS
0 | 8 | Disaster Recovery
0 | 18 | PHP/MySQL
2 | 3 | Security
2 | 7 | VPN's

And that's perfect because even when the cnt is zero, I still get the cat_title and it gives me the cat_id rather than NULL or just leaving the row out. I'm still working on that second query with the articles if you have any ideas.

Thanks for all your help. I really appreciate it.

[bastien]

can you dump the table structure and the sample data to me? I can ty it locally

[cgraz]

PM sent.