Getting all articles with a GROUP_CONCAT field of their tags?

There are at least two approaches you could use. One approach is to join with the tables twice. The other as you point out is to use a subquery.

For simiplicity and ease of reading, I'd probably go with the subquery here. The resulting query would look something like this: SELECT headline, GROUP_CONCAT(tags. Tag_name) AS all_tags FROM articles JOIN articles_tags ON articles.

Article_id = articles_tags. Article_id JOIN tags ON articles_tags. Tag_id = tags.

Tag_id WHERE articles. Article_id IN ( SELECT articles. Article_id FROM articles JOIN articles_tags ON articles.

Article_id = articles_tags. Article_id JOIN tags ON articles_tags. Tag_id = tags.

Tag_id WHERE tags. Tag_name = 'japan' ) GROUP BY articles. Article_id And here's the approach using more JOINs: SELECT a.

Headline, GROUP_CONCAT(t2. Tag_name) AS all_tags FROM articles a JOIN articles_tags at1 ON a. Article_id = at1.

Article_id JOIN tags t1 ON at1. Tag_id = t1. Tag_id AND t1.

Tag_name = 'tag1' JOIN articles_tags at2 ON a. Article_id = at2. Article_id JOIN tags t2 ON at2.

Tag_id = t2. Tag_id GROUP BY a. Article_id.

This works, thank you - but it took a minute to run! Hmm. Might need some better indexing... – Matt Andrews Aug 26 '10 at 20:28 @Matt Andrews: Yes this definitely needs indexing otherwise the subquery will be run multiple times.

You can also try the approach using joins. – Mark Byers Aug 26 '10 at 20:33 @Matt Andrews: I've updated my answer to include an approach using joins. – Mark Byers Aug 26 '10 at 20:42 @Matt Andrews: Interested to see which of the three is the most efficient... – OMG Ponies Aug 26 '10 at 20:56 Thanks guys - here's some rough benchmarks: First query by @Mark Byers (subquery) - ~50 seconds.

Second query by @OMG Ponies (EXISTS) ~ 0.5 seconds. Third query by @Mark Byers (joins) - ~0.009 seconds. Ding ding, we have a winner!

Thanks a ton to you both for the suggestions, you've been really helpful :) – Matt Andrews Aug 26 '10 at 21:00.

Using EXISTS: SELECT a. Headline, GROUP_CONCAT(t. Tag_name) AS all_tags FROM ARTICLES a LEFT JOIN ARTICLES_TAGS at ON at.

Article_id = a. Article_id LEFT JOIN TAGS t ON t. Tag_id = at.

Tag_id WHERE EXISTS(SELECT NULL FROM ARTICLES x JOIN ARTICLE_TAGS y ON y. Article_id = x. Article_id JOIN TAGS z ON z.

Tag_id = y. Tag_id AND z. Tag_name = 'japan' WHERE x.

Article_id = a. Article_id) GROUP BY a. Article_id No need to use LEFT JOINs in the subquery if you're only interested in the ones associated to the "japan" tag.

I get "Unknown column 'x. Tag_id' in 'on clause'" with this query. – Mark Byers Aug 26 '10 at 20:40 @Mark Byers: Thx, corrected.

– OMG Ponies Aug 26 '10 at 20:43 This is much quicker (0.2 sec), even after updating a few foreign keys and indexing. Just going to try Mark's other suggestion though. – Matt Andrews Aug 26 '10 at 20:47.

There are at least two approaches you could use. One approach is to join with the tables twice. The other as you point out is to use a subquery.

For simiplicity and ease of reading, I'd probably go with the subquery here. The resulting query would look something like this.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.