Using MAX() to return testimonial results that have a product topic added

[busboy]

This returns a list of testimonials that do NOT have a topic tag assigned to them (product = ‘Yes’ in the topics table). I’ve tried adjusting the MIN() / MAX() part of the query to give me just the opposite, a list of testimonials that do have at least one topic assigned that is designated as a product, but no success. Can someone spot something obvious that I’m overlooking?

Thanks!

 SELECT
 
 t.testimonialID,
 
 c.product,
 
 t.brandNeutral,
 
 t.sufficientDetail,
 
 t.usePhoto,
 
 t.healthRelated,
 
 t.title,
 
 t.compliantTitle,
 
 t.dateEditorEdit,
 
 t.dateAdminEdit
 
 FROM testimonials t
 
 INNER JOIN
 
 testimonialTopics b
 
 ON
 
 t.testimonialID = b.testimonialID

 INNER JOIN
 
 topics c
 
 ON
 
 b.topicID = c.topicID
 
 WHERE
 
 t.approved = 'Yes'
 
 and t.compliantTitle is null
 
 GROUP BY t.testimonialID
 
 HAVING
 
 MAX(c.product) < 'Yes'
 
 ORDER BY
 
 dateEditorEdit;

[r937]

try

HAVING MAX(c.product) = ‘Yes’

[busboy]

I tried that earlier. The query gives a different amount of resulting testimonials, but each have “No” or “Null” in the product column of the results. Could the problem be in the way I did my joins?

Thanks!

[r937]

@busboy

Could the problem be in the way I did my joins?

  FROM testimonials t
INNER 
  JOIN testimonialTopics b
    ON t.testimonialID = b.testimonialID
INNER 
  JOIN topics c
    ON b.topicID = c.topicID

no

[busboy]

Is it not possible to do the kind of query I’m wanting?

[r937]

of course it’s possible

[busboy]

Okay, I’m open to trying any other ideas that you have then. Thank you.

[r937]

you’re most welcome

[busboy]

I changed that second left join to right join, and that didn’t do the trick.

[busboy]

Can you help Rudy?

[r937]

please refer to an earlier post where i answered “no” to your question about whether the problem is in the way you did your joins

[busboy]

I did see that post where you said, “No”. And the portion of my query that you included is what I have as well. The only difference being the way you formatted the text. Forgive me, as I’m not bright enough to see what you’re getting at or suggesting.

[r937]

i’m not being obtuse

i honestly think the answer to your question has been provided

you said that “a list of testimonials that do NOT have a topic tag assigned to them (product = ‘Yes’ in the topics table)” was provided by

HAVING MAX(c.product) < 'Yes'

so you wanted the opposite, “a list of testimonials that do have at least one topic assigned that is designated as a product”

and in my opinion this is easily produced by

HAVING MAX(c.product) = 'Yes'

if this does not do what you want, i would suggest that you look at your data

closely

[busboy]

I initially thought adjusting my query did not work when changing it to:

HAVING MAX(c.product) = ‘Yes’

That’s because all the rows had “No” in the product column.

But when I actually checked several testimonials from the list, they indeed had various product topics added to them. I’m not exactly sure why the product column has “No” for all testimonials, but the query is now producing the list of testimonials I am looking for.

Thank you!