CODEWITHSUNDEEP
mysqlsql
sh3rifme
sh3rifme

Reputation: 1064

Attempting to query a tagging object(many to many table)

I have three tables that i am trying to query:

Mods

mod_id
author
url
name

Tags

tag_id
tag_name
tag_url

tags_to_mods(mods & tags have many to many relationship)

ttm_id
tag_id
mod_id

Essentially i am trying to find the tag_name and tag_id related to the given mod_id.

How do i go about doing this? I am quite a newbie with SQL and have yet to attempt querying properly relational data like this. I assume that there is a better way to do it than what i have opted to go for.

my attempt at this (in pseudo(ish) code):

x = SELECT tag_id FROM tags_to_mods WHERE mod_id = $mod_id

SELECT tag_name, tag_id FROM tags WHERE tag_id = x

it gets the desired result but i have to query my table twice...

Upvotes: 1

Views: 42

Answers (2)

Linger
Linger

Reputation: 15048

SELECT m.author, m.name, m.url, t.tag_id, t.tag_name
FROM Mods AS m
INNER JOIN tags_to_mods AS tm ON tm.mod_id = m.mod_id
INNER JOIN Tags AS t ON t.tag_id = tm.tag_id
WHERE m.mod_id = $mod_id

Upvotes: 0

Hackerman
Hackerman

Reputation: 12305

Try this(where $mod_id is the id of the Mod):

select tg.tag_name, tg.tag_id
from Tags tg
inner join tags_to_mods tm
on(tg.tag_id = tm.tag_id)
where tm.mod_id = $mod_id;

Upvotes: 1

Related Questions