CODEWITHSUNDEEP
mysqlsqldatabasejoin
Serghei Ghidora
Serghei Ghidora

Reputation: 473

Complex SQL query with JOIN

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).

Upvotes: 2

Views: 80

Answers (6)

Gavin Burke
Gavin Burke

Reputation: 149

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

Upvotes: 0

Magnus Wallstr&#246;m
Magnus Wallstr&#246;m

Reputation: 1529

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

Upvotes: 1

StanislavL
StanislavL

Reputation: 57381

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)

Upvotes: 0

KP.
KP.

Reputation: 393

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

Upvotes: 0

Utsav
Utsav

Reputation: 8093

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

Upvotes: 1

vaso123
vaso123

Reputation: 12391

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

Upvotes: 0

Related Questions