I have a table of songs, I have another table of tags and a last table with the relationship between the songs and the tags.
I need to get all the songs that contain the tagID of one or more tags, but it should return only one result per song, without duplicates.
Following the attached photos, I would need to obtain record 590 because it meets the condition of having labels 4 and 7. And not receive two records of 590, only one.
I have tried this query but it returns multiple records. And when I change OR to AND it doesn't return anything because I'm comparing the same column. Any solution?
SELECT * FROM tracks_tags WHERE tagId LIKE "%4%" OR tagId LIKE "%7%"
I leave an example of my tables.
TRACK TABLE
TAG TABLE
UPDATED, The previous query that I proposed as a solution was written in a fast way and did not seem quite correct to me.
The solution to your problem is:
Analyze this possible solution, it may be useful in what you want to do:
The key is to use the group_concat aggregation function and the group by clause , in this way you will show all tags that correspond to the same song, separating the tags by the ',' character . You would get something like this:
If you want the songs to have both tags, the sum (conditional aggregation) of searched tags must be 2, assuming that the tags are not repeated for each song.
For three labels I would change:
Just to propose an alternative approach, I would do it with mysql's JSON functions. Let be
tags_array
a field of type JSON (specifically, a json array) coming from a subquery, andarray_buscado
an array of ids that we want to filter.The function
JSON_CONTAINS(tags_array, array_buscado)
returns true when all elements ofarray_buscado
are present intags_array
.Populating the BBDD with songs and tags, such that
It looks like:
Then:
It will only show yesterday and penelope (the only classic AND sad ones on the list).
If you wanted to apply the equivalent of a
OR
, and we were on MySQL 8, the functionJSON_OVERLAPS
would list all songs where at least one item is among those searched for. In other words, "Sultans of Swing" would also have been listed because it complies with at least one element (Anglo).What I like about the syntax is that you could wrap the subquery in a view, and search it directly, without having to do the search and then the aggregation over and over again, which can be inefficient if the aggregation is expensive. Going from an AND set to an OR set affects only one function. No clauses need to be added or removed, everything is delegated via the argument.
obviously you can also search by ID
but I wanted to show matching by slugs because your business layer will receive or at least be able to compute the slugs, while the IDs are unknown in that part of the pipeline.
dejo un fiddle funcionando
PS: Of course the order of the elements is irrelevant. search
["triste","clásico"]
is equal to["clásico","triste"]
To get the songs that have tag 4 and 7, you could do 2
INNER JOIN
( one for each tag )Example:
If you need to get all songs that have at least one tag, this should work for you: