SQL : map column several times to item

Question

I am new to SQL and is trying to retrieve all names from mapped id | name table.

Structure looks like this:

My goal is to return a complete item with all columns, but the id columns are replaced with a real value.

I can use this code to retrieve 1 tag (only problem with tags since that's the only column that is not 1:1 mapped):

select Posts.Title as Title, Tags.Name as Tag1
from Posts 
inner join Tags on Posts.Tag1ID = Tags.TagID

But I can't figure out how to get all values from Tag1ID, Tag2ID, Tag3ID and Tag4ID

Answer 1

This is a poor data model, but you can use join . . . and join again and again:

select p.Title as Title, t1.Name as Tag1, t2.Name as Tag2,
       . . .
from Posts p left join
     tags t1
     on p.Tag1ID = t1.TagID left join
     Posts p left join
     tags t2
     on p.Tag1ID = t2.TagID left join
     . . .

You need to repeat this for every tag column.

If you can, you should fix the data model! A better data model would have a PostTags table with one row per post and per tag.