SQL: help with sql joins
Hi I have an app that has posts, tags, and a tag_ref link table. I want to query for posts that have a specific tag association.
The database structure is as follows:
posts
id
tags_ref
row_id
table
tag_id
tags
id
safe_tag
tag
My query basically goes if $safe_tag is not null then join tags_ref on post.id = tags_ref.row_id, join tags on tags_ref.tag_id = tags.id, where tags_ref.table = 'posts' and tags.safe_tag = 'food' and post.city_id = 2
Is this query correct? Am I using 开发者_StackOverflow社区the correct type of join?
SELECT *
FROM (`posts`)
INNER JOIN `tags_ref` ON `posts`.`id` = `tags_ref`.`row_id`
INNER JOIN `tags` ON `tags_ref`.`tag_id` = `tags`.`id`
WHERE `tags_ref`.`table` = 'posts'
AND `tags`.`safe_tag` = 'food'
AND `posts`.`city_id` = '2'
I'm getting odd results that have no tags associated at all.
Thanks
This will only return results from the posts
table, as opposed to all columns in the join tables. I have removed the city_id
constraint that you haven't specified in your question text (and that was in your query):
SELECT P.*
FROM posts AS P
INNER JOIN tags_ref AS TR ON P.id = TR.row_id
INNER JOIN tags AS T ON TR.tag_id = T.id
WHERE TR.table = 'posts'
AND T.safe_tag = 'food'
I have also added table aliases and removed the quoting.
If you do not get any results, that would suggest to me that there are no posts with the food
.
精彩评论