Jump to content

select from table with output from another table


Recommended Posts

I have two table -
categories - cat-id, cat-title
posts - post-id, post-cat-id, post-title

I want to search through all rows in 'posts' and get the number of posts based on each category in 'categories',  echo 'cat-title' - number of posts

if no posts then echo 'cat-title' - "no posts".

posts are assigned a post-cat-id relative to cat-id.

I tried using joins WHERE cat-id = post-cat-id AND cat-title = 'a-category' but that only shows results when posts are assigned 'a-category'.

If that category does not exist in 'posts' then nothing shows

How, in your opinion, should I approach this?

Thanks

Link to post
Share on other sites

Use a LEFT JOIN

SELECT cat_title
     , CASE WHEN COUNT(post_id) = 0 THEN 'No posts'
            ELSE COUNT(post_id)
       END as total
FROM categories c
     LEFT JOIN
     posts p ON c.cat_id = p.post_cat_id
GROUP BY c.cat_title;

I would also recommend using using valid column names.

Link to post
Share on other sites

Thanks for that. I'll give it a go this evening.

What did you mean by "I would also recommend using using valid column names"? I was trying to keep everything descriptive so I have a better understanding of what I am learning.

Thanks

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.