Complex SQL query with JOIN

Question

I have the following tables in my database:

tags

id | name
---------
 1 | tag1
 2 | tag2
 3 | tag3
 4 | tag4

map_posts_tags

post_id | tag_id
----------------
    123 | 1
    123 | 2
    234 | 1
    345 | 3
    345 | 4
    456 | 2
    456 | 1

Is it possible to get all the posts with the same related tags as the passed post_id by using a SQL query?

For example:
I have my post with id 123 and want to get a list of all the posts that have the same tags related (excluding from the list my post with id 123 if possible from SQL).

If you have post with id 123, what's the expected result?

jarlh
– jarlh

2016-04-11 08:52:09 +00:00
Commented Apr 11, 2016 at 8:52 — jarlh
– jarlh, Commented Apr 11, 2016 at 8:52
all of the tags? in your list only 456 qualifies right??

Zahiro Mor
– Zahiro Mor

2016-04-11 08:52:26 +00:00
Commented Apr 11, 2016 at 8:52 — Zahiro Mor
– Zahiro Mor, Commented Apr 11, 2016 at 8:52
@ZahiroMor in my list 234 and 456 qualifies,

Serghei Ghidora
– Serghei Ghidora

2016-04-11 08:53:48 +00:00
Commented Apr 11, 2016 at 8:53 — Serghei Ghidora
– Serghei Ghidora, Commented Apr 11, 2016 at 8:53

Magnus Wallström · Accepted Answer · 2016-04-11 09:06:49Z

1

SELECT * FROM tags t 
    INNER JOIN map_posts_tags mpt
    ON t.id = mpt.tag_id
WHERE tag_id in (
    select tag_id from map_posts_tags
    where post_id = 123)
AND NOT post_it = 123

answered Apr 11, 2016 at 9:06

Magnus Wallström

1,5894 gold badges15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Utsav · Accepted Answer · 2016-04-11 09:01:44Z

1

This will give you what you want. You can join it with tags table to get tag name too

SQLFiddle Demo

select distinct post_id from  map_posts_tags where tag_id in 
(select tag_id from map_posts_tags where post_id = 123)
and post_id <> 123

answered Apr 11, 2016 at 9:01

Utsav

8,1632 gold badges22 silver badges40 bronze badges

2 Comments

StanislavL Over a year ago

your query returns all posts with at least one tag of the post 123

Utsav Over a year ago

This is what OP wants if I understand the question correctly.

vaso123 · Accepted Answer · 2016-04-11 08:54:42Z

0

If I understood your question clearly:

SELECT map_posts_tags.post_id FROM map_posts_tags 
INNER JOIN tags ON tags.id = map_posts_tags.tag_id
WHERE map_posts_tags.tag_id = 1
AND map_posts_tags.post_id != 123

answered Apr 11, 2016 at 8:54

vaso123

12.4k4 gold badges37 silver badges68 bronze badges

1 Comment

Serghei Ghidora Over a year ago

this is by passing the tag id and post id to exclude. I would need to pass only the post_id if possible . Maybe nested selects are the solution

KP. · Accepted Answer · 2016-04-11 09:03:34Z

0

The below query should work for you

  select t.*, mpt.* from tags t
    join map_posts_tags mpt on t.id = mpt.tag_id
    where mpt.tag_id in (
       select tag_id from map_posts_tags where post_id = 123)
    and mpt.post_id  123

answered Apr 11, 2016 at 9:03

KP.

3931 silver badge12 bronze badges

Comments

StanislavL · Accepted Answer · 2016-04-11 09:04:18Z

0

select post_id 
from map_posts_tags mpt 
where tag_id in 
            (select tag_id 
             from map_posts_tags 
             where post_id = 123)
  and post_id <> 123
group by post_id
having count(*)=(select count(*)
                 from map_posts_tags 
                 where post_id = 123)

answered Apr 11, 2016 at 9:04

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Comments

Gavin Burke · Accepted Answer · 2016-04-11 09:07:25Z

0

Not sure what the purpose of the tags table is right now but the following gives you all post_id's and tag_id's that match your selected post_id

DECLARE @post_id int
SET @post_id = 123
select * from map_posts_tags
where tag_id in (select tag_id from map_posts_tags where post_id = @post_id)
and post_id <> @post_id

As others have stated, you could join back onto the tags table if you're wanting tag names too

JOIN tags on tags.id = map_posts_tags.tag_id

answered Apr 11, 2016 at 9:07

Gavin Burke

1491 gold badge2 silver badges7 bronze badges

Collectives™ on Stack Overflow

Complex SQL query with JOIN

6 Answers 6

Comments

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related