-1

I have 3 tables users, tags, user_tags. I try to fetch users with theirs tags with next query:

SELECT
        u.*,
        t.id    AS tag_id,
        t.value AS tag_value
FROM (SELECT *
      FROM users
      WHERE type = 1
      LIMIT 10
      OFFSET 0
     ) u LEFT OUTER JOIN (user_tags ut INNER JOIN tags t ON t.id = ut.tag_id)
                ON u.id = ut.user_id;

But I want to add condition to fetch users only if they have the specified tag. I tried to do it with next:

SELECT
        u.*,
        t.id    AS tag_id,
        t.value AS tag_value
FROM (SELECT *
      FROM users
      WHERE type = 1
      LIMIT 10
      OFFSET 0
     ) u LEFT OUTER JOIN (user_tags ut INNER JOIN tags t ON t.id = ut.tag_id)
                ON u.id = ut.user_id AND ut.tag_id = :tag_id;

But in this case I get fetch users without the rest of their tags. Please advise a good practice to fetch users by specified tag.

2

You should modify the subquery (that chooses the 10 users), not the main query (that fetches all the tags of the specific users):

SELECT
        u.*,
        t.id    AS tag_id,
        t.value AS tag_value
FROM (SELECT ui.*
      FROM users AS ui
      WHERE ui.type = 1
        AND EXISTS
            (SELECT 1 FROM user_tags AS uti 
             WHERE ui.id = uti.user_id
               AND uti.tag_id = :tag_id
            )
      LIMIT 10
      OFFSET 0
     ) AS u 
     -- the rest of the query (un)changed
  LEFT JOIN user_tags AS ut  ON u.id = ut.user_id
  LEFT JOIN tags AS t  ON t.id = ut.tag_id ;
  • Thank you, I know that solution, I tried to do it without subquery in subquery:) – Dmitro Feb 20 '18 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.