MySQL: grab one row from each category, but remove duplicate rows posted in multiple categories

Question

Hi all.

I have a database of articles, which are stored in categories. For my homepage, I want to grab an article from each category (I don't care which). However, some articles are crossposted to multiple categories, so they come up twice.

I have a table called tblReview with the article fields (reviewID, headline, reviewText) and a table called tblWebsiteContent that tells the site which categories the articles are in (id, reviewID, categoryID) and finally, a table called tblCategories (categoryID, categoryName) which stores the categories.

My query basically joins these tables and uses GROUP BY tblCategory.categoryID. If I try adding 'tblReview.reviewID' into the GROUP BY statement, I end up with hundreds of articles, rather than 22 (the number of categories I have).

I have a feeling this needs a subquery but my test efforts haven't worked (not sure which query needs to contain my joins / field list / where clause etc).

Thanks!

Matt

Answer 1

Try using SELECT DISTINCT. (This will only work if your SELECT is only pulling the article ID.)

Answer 2

select DISTINCT reviewID

Answer 3

SELECT T.categoryName, tR.headline, tR.reviewText
FROM (
    SELECT tC.categoryName, MAX(tR1.reviewID) reviewID
    FROM tblReview tR1 join tblWebsiteContent tWC on tR1.reviewID = tWC.reviewID
                      join tblCategory tC on tC.categoryID = tWC.categoryID
    GROUP BY tC.categoryName) T JOIN
    tblReview.tR on tR.reviewID = T.reviewID

this query will select for each category an article headline corresponding to the Max reviewId for that category (you said 'I don't care which')

Answer 4

you can use the select distinct as a subselect, no?