Skip to content
Advertisement

SQL Query: Search Across Multiple Fields in JOINed Tables

SQL Version: MySQL 8.0 or SQL Server

SQL Fiddle: https://www.db-fiddle.com/f/wcHeXkcynUiYP3qzryoYJ7/6

I have a table of images and a table of tags that link to those images.

I want to be able to pass (2) tags to the query and have it select only the images that match BOTH tags. For example, I want to pass November and 2021 and have it return only 1.jpg.

If I do something like this:

But that returns:

How can I rewrite this query to only get images that match both tags?

Advertisement

Answer

The problem is your data is related across different rows. If all the data was in the same row then it’d be easy

When it’s in different rows you want to get both rows like you’re doing..

..but then you only want those images for which there are two rows. If there is only one row (eg only Nov or only 2021) you don’t want that

There are various ways to do it. One is to join the tags table to itself, having filtered one of the sides to just months and the other to just years

This would implicitly put month nov and year 2021 “on the same row” so only images with both those tags would appear in the join result..

..but probably the usual way we do such “across row” queries is to check the count after grouping them, or check that the min is x and the max is y, for example:

Or

The count thing works if the tag names are distinct. If you can double up November by accident then it’ll pick those up too. The min max only works for two tags.. you can also use something like

And that’s good for any number of criteria, you just go up in powers of two, so for 3 tags you case when 1,2,4 and demand the sum be 7, you could also go in powers of anything, like base 10.. go up in 1,10,100 and demand the sum be 111..

You can also ask that there exists a related row multiple times:

EXISTS returns true if there is a row that meets the criteria: his sql means “images where there is some tag row that is November and there is some(other) tag row that is 2021”


Whatever you do, you need to think of a way to group the data up across the N rows where it exists and then do something that means the rows as a group meet the criteria. That’s a trick because we don’t usually think in those set terms as humans, we tend to think more “row by row”

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement