Unique is not working in this query

[busboy]

What am I doing wrong with this query? The results will sometimes return the same row.

Thanks!

	SELECT
		distinct studies.studyID,
		studies.summary,
		studies.link,
		topics.topic
	FROM 
		studies 
	LEFT join 
		studyTopics 
	ON 
		studyTopics.studyID = studies.studyID
	LEFT JOIN
		testimonialTopics
	ON
		testimonialTopics.topicID = studyTopics.topicID
	LEFT JOIN
		topics
	ON
		topics.topicID = studyTopics.topicID
	LEFT JOIN
		testimonials
	ON
		testimonials.testimonialID = testimonialTopics.testimonialID
	WHERE
		testimonials.testimonialID = '4353'
	LIMIT
		50;

[r937]

@busboy

What am I doing wrong with this query? The results will sometimes return the same row.

same row? impossible

what are you doing wrong? adding a WHERE condition on the right table in a LEFT OUTER JOIN

pls explain why you want LEFT OUTER JOINs – it seems to me that you want to start your FROM clause with the testimonials table, and build up from there using INNER JOINs

[guelphdad]

Also DISTINCT works on all columns selected and not just the one column. Just mentioning that as I’ve seen many posts where people do not understand that.

[r937]

excellent point, guelphdad

if that were indeed the case here, then OP might be looking for only one topic per study

the query gets weirder the longer i look at it – get all studies related to all topics for a particular testimonial? it doesn’t make any sense

played any frisbee golf lately?

[busboy]

@r937

same row? impossible

I’m sorry this query caused some confusion. Each testimonial is assigned multiple topics. Each scientific study is assigned multiple topics. I’m just trying to see a list of scientific studies that should be shown for a given testimonial. So if the testimonial is tagged with the topic of acne, and if there are 3 scientific studies that are also tagged with the topic of acne, then the results should show three rows. Currently the results will sometimes return the same scientific study more than once.

[r937]

likely because other topics are getting returned

[busboy]

That was my thought as well. A testimonial may have the topic of acne, skin and blemishes. Some scientific studies may also match two or more of those same topics.

So what is the best way to ensure that only one scientific study is returned, even though it may match multiple topics? My attempt at using DISTINCT apparently did not work.

Thank you.

[r937]

try this –

SELECT studies.studyID , studies.summary , studies.link , topics.topic FROM testimonialTopics INNER JOIN topics ON topics.topicID = testimonialTopics.topicID INNER JOIN studyTopics ON studyTopics.topicID = topics.topicID INNER JOIN studies ON studies.studyID = studyTopics.studyID WHERE testimonialTopics.testimonialID = 4353

[busboy]

@r937

try this –

I tried it and it produces the exact same results as my original query. Any other suggestions?

Thanks!

[r937]

@busboy

@r937

try this –

I tried it and it produces the exact same results as my original query. Any other suggestions?

Thanks!

oh joy!! that means your data is fine and your query, even with left joins, was correct

also, note that “the exact same” results on my side did not use DISTINCT, whereas your query did, which shows that DISTINCT was not necessary…

… and there are no duplicates

[r937]

do me an extra favour, please, and try this –

SELECT studies.studyID
     , studies.summary
     , studies.link
     , GROUP_CONCAT(topics.topic) AS topics
  FROM testimonialTopics
INNER
  JOIN topics
    ON topics.topicID = testimonialTopics.topicID 
INNER
  JOIN studyTopics
    ON studyTopics.topicID = topics.topicID
INNER
  JOIN studies
    ON studies.studyID = studyTopics.studyID
 WHERE testimonialTopics.testimonialID = 4353 
GROUP
    BY studies.studyID

[busboy]

Brilliant! Do you realize you’ve been helping me for over ten years? Thanks r937!