Need help showing testimonials that don't have certain topic tags assigned to them

I’ve been using the following query to show testimonials that need topics to be assigned to them by my employee. Topics like headache, back-pain, acne, etc. Halfway through the project we decided to also add tags for the essential oils mentioned in each testimonial, like lavender-oil, lemon-oil, etc.

All of the testimonials have at least one tag now, but have the database still needs oil specific tags added in addition to the general topic tags already assigned. The tables involved in this project are:

testimonials
topics
testimonialTopics (which is a junction table)

I need to modify the following query so that it will show the next testimonial in line that needs an oil specific tag added to it. In my topics table I added a column called “product”. Then I added ‘Yes’ to all rows where the topic is an oil, like lavender-oil, as opposed to non-product topics, like headache or acne. So the query needs to show the next testimonial where the assigned tags to it are product = ‘No’. In other words, the moment my employee adds an tag that is product = ‘Yes’, then that testimonial should not appear in the results when this query is rerun. I hope this makes sense.

Can someone help me with this? Although I’ve learned a lot about databases over the years, it is not my strong point, since I have to learn all kinds of other topics needed to run a business (email marketing, SEO, etc.) So this modification is a bit over my head.

Thanks in advance!

		SELECT
			t.testimonialID,
			t.title,
			t.body,
			t.keywords
		FROM testimonials t
		LEFT JOIN
			testimonialTopics b
		ON
			t.testimonialID = b.testimonialID
		WHERE
			t.approved = 'Yes'
			and b.testimonialID is null
		LIMIT
			1;

(emphasis mine)

(likewise)

… you’ve already answered your own question, and have an example of how to add it. The database gurus will be along shortly to tell you you should be using a boolean instead of a varchar to store this information, but you’ve already made the direct answer to your question :wink:

1 Like

I think you misunderstood what I’m trying to accomplish. My query does not have an example of how to add it. My query doesn’t even reference the 3rd table that will need to be incorporated through another join, the topics table. It’s not a matter of including an additional item to the where clause. It’s much more complex than that.

Thank you though.

You’ve already got an example of a join there too. Join the extra table, and add the extra where clause.

Here is my best guess, but when I run it there are no results. Now what @m_hutley?

		SELECT
			t.testimonialID,
			t.title,
			t.body,
			t.keywords
		FROM testimonials t
		LEFT JOIN
			testimonialTopics b
		ON
			t.testimonialID = b.testimonialID
		LEFT JOIN
			topics c
		ON
			b.topicID = c.topicID
		WHERE
			t.approved = 'Yes'
			and c.product = 'No'
		LIMIT
			1;

Can you show me an entry in your 3 tables that should meet this criteria?

1 Like

When you get zero or questionable results, always look at your WHERE query FIRST!!! That’s where you start!

Here is my latest query. The results are showing a row for each topic that is assigned to a given testimonial, which is not what I’m trying to do. I need a single row to be shown for each testimonial that does not have a product topic assigned to it…even though it may have non-product topics assigned to it. A regular topic would be something like spine or back-pain. A product topic is something like lavender-oil, or lemon-oil.

SELECT
	t.testimonialID,
	t.title,
	c.topic
FROM testimonials t
LEFT JOIN
	testimonialTopics b
ON
	t.testimonialID = b.testimonialID
LEFT JOIN
	topics c
ON
	b.topicID = c.topicID
WHERE
	t.approved = 'Yes'
	and c.product = 'No'
LIMIT
	100;

Ah okay i see what you’re saying now. Yup, little more complex of a query.

So now we need to GROUP the results by testimonialID. Which makes sense, we only want each testimonial to be considered as a sum of its products.

What i’m going to say next may vary slightly depending on which database system you’re using. In lieu of a declared engine, I assume a generic one that allows aggregation of varchar fields lexicographically. This will allow us to operate on the ‘product’ column without it being a boolean value.

Simply put, we’re going to group all of the testimonial-topics together, and ask if the Maximum value of the product column for that group is less than ‘Yes’. (Other values the column may contain are: “No”, and NULL)

(Query untested)

SELECT
	t.testimonialID,
	t.title
FROM testimonials t
LEFT JOIN
	testimonialTopics b
ON
	t.testimonialID = b.testimonialID
LEFT JOIN
	topics c
ON
	b.topicID = c.topicID
WHERE
	t.approved = 'Yes'
GROUP BY t.testimonialID
HAVING 
    MAX(c.product) < 'Yes'
LIMIT
	100;
1 Like

I’m stunned @m_hutley. Your intelligence is way above mine. Thank you so much for helping me! You rock!

2 Likes

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