Using two different junction tables in a single query

I have three regular tables:
testimonials (testimonialID)
studies (studyID)
topics (topicID, topic)

I have two junction tables:
studyTopics (studyID, topicID)
testimonialTopics (testimonialID, topicID)

I want to write a query that displays the topicID, topic, count of how many times that topic appears in studyTopics, count of how many times that topic appears in testimonialTopics.

I have put together several other queries that make use of the junction tables, but what I’m trying to do know is a little over my head.

Any help would be greatly appreciated!

Thanks!

definitely a tricky issue

if you join all tables in one query, you’ll get cross-join effects and wildly inflated counts

you’ll need at least one grouping subquery – i use two for thses situations

Can you offer a tad bit more to go on?

Thanks!

[quote=“busboy, post:3, topic:198047, full:true”]
Can you offer a tad bit more to go on?[/quote]

sure thing…

[quote]
I want to write a query that displays the
topicID, topic,
count of how many times that topic appears in studyTopics,
count of how many times that topic appears in testimonialTopics.[/quote]

SELECT topics.topicID , topics.topic , COALESCE(topic_studies.studies_count,0) AS studyTopics_count , COALESCE(topic_testimonials.testimonialID_count,0) AS testimonialTopics_count FROM topics LEFT OUTER JOIN ( SELECT topicID , COUNT(*) AS studies_count FROM studyTopics GROUP BY topicID ) AS topic_studies ON topic_studies.topicID = topics.topicID LEFT OUTER JOIN ( SELECT topicID , COUNT(*) AS testimonials_count FROM testimonialTopics GROUP BY topicID ) AS topic_testimonials ON topic_testimonials.topicID = topics.topicID

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.