I have a table of topics, and I also have two junction tables. I’m trying to write a query that shows the name of the topic, and how many testimonials and scientific studies are assigned to it. I’m close, but the results are not what I’m expecting. Am I using the wrong type of join for this situation?
Thanks!
SELECT
a.topicID,
a.topic,
count(*) as numberOfTestimonialsAssigned,
count(*) as numberOfStudiesAssigned
FROM
topics a
LEFT JOIN
testimonialtopics b
ON
a.topicID = b.topicID
LEFT JOIN
studytopics c
ON
b.topicID = c.topicID
GROUP BY
a.topicID
HAVING
numberOfTestimonialsAssigned > 0
LIMIT 10;
[quote=“busboy, post:1, topic:202200, full:true”] Am I using the wrong type of join for this situation?[/quote]the problem is, you’re joining to two one-to-many relationships at the same time, and thereby getting cross join effects
you’ll have to do the counting in subqueries –
SELECT a.topicID
, a.topic
, b.numberOfTestimonialsAssigned
, COALESCE(c.topics,0) AS numberOfStudiesAssigned
FROM topics AS a
INNER
JOIN ( SELECT topicID
, COUNT(*) AS numberOfTestimonialsAssigned
FROM testimonialtopics
GROUP
BY topicID ) AS b
ON b.topicID = a.topicID
AND b.numberOfTestimonialsAssigned > 0
LEFT OUTER
JOIN ( SELECT topicID
, COUNT(*) AS topics
FROM studytopics
GROUP
BY topicID ) AS c
ON c.topicID = a.topicID
So I took your example, which works great, and tried to apply it to a slightly different situation. This time I would like to list the title of each scientific study, and then show how many times its assigned to topics and testimonials. This is what I came up with, which gives the error of , “Unknown column ‘b.studyID’ in on clause.”. I’ve been trying to figure this out for a few hours, but I think I hit a dead end.
SELECT a.studyID
, a.title
, COALESCE(b.topicsTotal,0) AS topics
, COALESCE(c.testimonialsTotal,0) AS testimonials
FROM studies AS a
LEFT
JOIN ( SELECT topicID
, COUNT(*) AS topicsTotal
FROM studyTopics
GROUP
BY topicID ) AS b
ON a.studyID = b.studyID
LEFT OUTER
JOIN ( SELECT testimonialID
, COUNT(*) AS testimonialsTotal
FROM testimonialTopics
GROUP
BY testimonialID ) AS c
ON c.topicID = b.topicID;
Ah, you found what I overlooked. I fixed the query but then it complained about the “c” subquery. So I tweaked that one and got it all working Thanks for your help!!!
Here is what I ended up with:
SELECT a.studyID
, a.title
, COALESCE(b.topicsTotal,0) AS topics
, COALESCE(c.testimonialsTotal,0) AS testimonials
FROM studies AS a
LEFT
JOIN ( SELECT studyID, topicID
, COUNT(*) AS topicsTotal
FROM studyTopics
GROUP
BY studyID ) AS b
ON a.studyID = b.studyID
LEFT OUTER
JOIN ( SELECT topicID
, COUNT(*) AS testimonialsTotal
FROM testimonialTopics
GROUP
BY topicID ) AS c
ON c.topicID = b.topicID;
r937, I’m back for more. I just noticed today that the counts in the testimonials column are not always correct, but sometimes they are. Do you have any idea what I’m missing? The topics column is giving correct totals.
Thanks for your help!
SELECT a.studyID
, COALESCE(b.topicsTotal,0) AS topics
, COALESCE(c.testimonialsTotal,0) AS testimonials
FROM studies AS a
LEFT
JOIN ( SELECT studyID, topicID
, COUNT(*) AS topicsTotal
FROM studyTopics
GROUP
BY studyID ) AS b
ON a.studyID = b.studyID
LEFT OUTER
JOIN ( SELECT topicID
, COUNT(*) AS testimonialsTotal
FROM testimonialTopics
GROUP
BY topicID ) AS c
ON c.topicID = b.topicID
ORDER BY
a.dateAdded desc;
[quote=“busboy, post:10, topic:202200, full:true”]
I just noticed today that the counts in the testimonials column are not always correct, but sometimes they are. Do you have any idea what I’m missing? [/quote]
it’s gotta be your data, i can’t see any structural difference in that subquery as compared with the other one
well, except that in the “b” subquery, you shouldn’t have topicID in the SELECT clause
could you confirm that “b” should use studyID and “c” should use topicID
Well, if I take out topicID from the “b” subquery, then I get the following error:
Unknown column ‘b.topicID’ in ‘on clause’
This query that I came up with works fine, and correctly displays the testimonials that are matched up with Scientific Study #116, because of the topics that are assigned to both the testimonials and studies:
SELECT DISTINCT
a.testimonialID,
a.title,
a.views,
date_format(a.dateAdded, '%m-%d-%Y') as addedDate
FROM
testimonials a
LEFT JOIN
testimonialTopics b
ON
a.testimonialID = b.testimonialID
LEFT JOIN
studyTopics c
ON
b.topicID = c.topicID
LEFT JOIN
studies d
ON
c.studyID = d.studyID
WHERE
d.studyID = '116'
Now I just need to get this query to come up with the correct testimonial totals. Like I said, it’s correctly showing the number of topics. This query displays each of the scientific studies, and then shows how many topics and testimonials are making use of each of those studies. Does this make sense?
SELECT a.studyID
, COALESCE(b.topicsTotal,0) AS topics
, COALESCE(c.testimonialsTotal,0) AS testimonials
FROM studies AS a
LEFT
JOIN ( SELECT studyID, topicID
, COUNT(*) AS topicsTotal
FROM studyTopics
GROUP
BY studyID ) AS b
ON a.studyID = b.studyID
LEFT OUTER
JOIN ( SELECT topicID
, COUNT(*) AS testimonialsTotal
FROM testimonialTopics
GROUP
BY topicID ) AS c
ON c.topicID = b.topicID
ORDER BY
a.dateAdded desc;