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.
[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