CODEWITHSUNDEEP
mysql
sander
sander

Reputation: 53

Mysql Query - Searching in a joined table without filtering records

I query a table where each row has multiple tags:

SELECT
    user.*,
    GROUP_CONCAT(tag.tag SEPARATOR ", ") as `tags`
FROM
    user
LEFT JOIN
    user_tag
ON
    user_tag.user_id = user.id
LEFT JOIN
    tag
ON
    tag.id = user_tag.tag_id
GROUP BY
    user.id

Now I want to be able to search users by tags. So after adding the where, it looks like this:

SELECT
    user.*,
    GROUP_CONCAT(tag.tag SEPARATOR ", ") as `tags`
FROM
    user
LEFT JOIN
    user_tag
ON
    user_tag.user_id = user.id
LEFT JOIN
    tag
ON
    tag.id = user_tag.tag_id
WHERE
    tag.tag LIKE "%engineer%" OR
    tag.tag LIKE "%programmer%"
GROUP BY
    user.id

This however means (obviously) that if a result is found, the tag table isn't returning all of the users tags anymore. So the selected column tags might look like "php programmer", even though the user has more tags.

Is there any way to search the joined table without actually limiting the returned results without using subqueries?

Upvotes: 0

Views: 145

Answers (2)

origo
origo

Reputation: 533

My guess here is that if you want to avoid a subquery you could use a HAVING clause with "tags", like so:

SELECT
    user.*,
    GROUP_CONCAT(tag.tag SEPARATOR ", ") as `tags`
FROM
    user
LEFT JOIN
    user_tag
ON
    user_tag.user_id = user.id
LEFT JOIN
    tag
ON
    tag.id = user_tag.tag_id
GROUP BY
    user.id
HAVING
    tags LIKE "%engineer%" OR tags LIKE "%programmer%"

Upvotes: 1

escargot agile
escargot agile

Reputation: 22399

You can add another JOIN with the tags like this:

SELECT
    user.*,
    GROUP_CONCAT(user_tags_to_return.tag SEPARATOR ", ") as `tags`
FROM
    user
LEFT JOIN
    user_tag user_tags_to_filter
ON
    user_tags_to_filter.user_id = user.id
LEFT JOIN
    tag tags_to_filter
ON
    tags_to_filter.id = user_tags_to_filter.tag_id
WHERE
    tags_to_filter.tag LIKE "%engineer%" OR
    tags_to_filter.tag LIKE "%programmer%"
LEFT JOIN user_tag user_tags_to_return
ON user_tags_to_return.user_id = user.id
LEFT JOIN tag tags_to_return
ON tags_to_return.id = user_tags_to_return.tag_id
GROUP BY
    user.id

Upvotes: 0

Related Questions