[registered_user]

I'm trying to learn mySQL, so I forced myself to read a book that covered the basics. To apply that knowledge, I figured that I'd make my first project a simple blog/cms. I started it some months ago, and I got frustrated with a query and put it on the back-burner. Now I'm trying to breathe some life back into it, and the same query is haunting me.

I've got two tables. art and post. art is for the articles, and it contains several columns, among them are art_id (primary key), art_date, and art_headline. In the post table, there are similar columns, but there is also post_art_id which correlates the post with the article (I forget the proper name for this type of column - secondary key or something). So, if I have an article that has an id of 15 in the article table, and if there is a comment on that article, then in the post table, a record's post_art_id will be 15. It is quite possible to have 10 comments on one article, so it's possible that many records in the post table will have a post_art_id of 15.

That brings me to my query. Ideally, I'd like to tackle the query in one shot and I'd like the query to fetch the article(s), and the number of posts (comments) associated with each article. I can't for the life of me get it to work properly.

Here's what I've got now:

SELECT art.art_id, art.art_date, art.art_headline, COUNT(DISTINCT post.post_id)
FROM art, post
WHERE art.art_id = post.post_art_id
GROUP BY art.art_id
UNION
SELECT art.art_id, art.art_date, art.art_headline, 0
FROM art
GROUP BY art.art_id
ORDER BY art.art_date DESC
LIMIT 0 , 10

The problem is that when the limit is set low (as here it's set to 10) It doesn't return any of the newer records that have 0 comments. When I increase the limit, it returns all the articles as expected, however, it returns some as duplicates. For example, art_id 15 might be returned twice. Once with the number of comments, then again with 0 comments.

I've written and rewritten that query more times than I care to guess, and I've yet to the get the desired result.

Is the query I'm after even possible with mySQL? I know I can make it work with separate queries for articles and posts, but I'd really prefer to take care of it one shot if at all possible.

Thanks in advance.

[Simon Mundy]

Originally posted by registered_user:
Here's what I've got now...

SELECT art.art_id, art.art_date, art.art_headline, COUNT(DISTINCT post.post_id)
FROM art, post
WHERE art.art_id = post.post_art_id
GROUP BY art.art_id
UNION
SELECT art.art_id, art.art_date, art.art_headline, 0
FROM art
GROUP BY art.art_id
ORDER BY art.art_date DESC
LIMIT 0 , 10

How about:-

SELECT art.art_id, art.art_date, art.art_headline, COUNT(post.post_id)
FROM art
LEFT JOIN post
ON art.art_id = post.post_art_id
GROUP BY post.post_art_id
ORDER BY art.art_date DESC
LIMIT 0 , 10

The way I read your query, all you're doing is creating a UNION for two recordsets - one with a post count and one without. Did I understand right?

[registered_user]

Well, to be honest, I'm not sure about the Union at all. It was suggested that I try to use the Union on another forum. (I was trying a left join before). But that's what the Union seems to do.

I don't really know if union is the most appropriate join to tell the truth. But when I try to just join the data, I only get articles with comments, so I figured union is the key to pulling records that have comments and records that do not have comments.

I tried your query, and it pulled only 5 records.

My query results are just weird. I attribute that to my lack of knowledge with SQL, but I'm gonna comb over my database just to make sure the data is as it should be.

I've pulled a good bit of hair out so far, but this is how I (try to) learn. Thanks for the help!

[registered_user]

Well, I've been trying to work this out on another forum as well, and I was given this to work with:

SELECT art.art_id, art.art_cat_id, art.art_file_id, art.art_copy, art.art_date, art.art_moddate, cat.cat_name, user.user_name, count( post.post_art_id ) AS comment_count
FROM art, cat, user
LEFT JOIN post ON post.post_art_id = art.art_id
GROUP BY art.art_id DESC

Actually, I was given a bit more than that, but I stripped it down to that. This query is almost perfect, however, it returns the oddest thing.

It returns the comment_count as its own column, but it multiplies the result by 4. So if there are no comments, then the result is 0, 1 comment returns as 4, 2 as 8, 3 as 12.

I'm running the queries in phpmyadmin, so I don't think it's any php of mine that's causing the odd multiplication.

It's most perplexing.

[registered_user]

apparently, it was multiplying by the number of categories, so when I removed the categories from the query, it worked as expected. I'm all good. Thanks for the help!