CREATE TABLE post ( `post_id` INT NOT NULL PRIMARY KEY, `title` VARCHAR(50) ); CREATE TABLE tags ( `tag_id` INT NOT NULL PRIMARY KEY `tag_name` VARCHAR(20), ); CREATE TABLE post_tags ( `post_id` INT NOT NULL, `tag_id` INT NOT NULL, PRIMARY KEY(`person_id`, `tag_id`) );
The post_tags table contains the foreign keys of tags and posts. Something like this (:
To retrieve a post and all its tags, you need to JOIN the tables in your queries:
SELECT p.*, t.* FROM post p INNER JOIN post_tags pt ON pt.post_id = p.post_id INNER JOIN tags t ON t.tag_id = pt.tag_id
This answer is adapted from How to store arrays in MySQL from StackOverflow.
Thank you for sharing the code sample.
But, I want to share multiple tags for single post.
To retrieve a post and all its tags, you need to JOIN the tables in your queries
On using the INNER JOIN clause it doesn't get the data if specific post have multiple tag_id
INNER JOIN
tag_id
SELECT * FROM post INNER JOIN tag ON post.post_id = tag.post_id;
it only return the data for 3rd row according to the above image.
INNER JOIN tag
Can you see the mistake here?
You need to do the JOIN with the post_tags table (with both references, posts and tags) to work.
Yeah I know that,
I tried with the both with and without post_tag table, it was return 0 rows.
post_tag
This is the best solution on the topic. +1
Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink.
Hide child comments as well
Confirm
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
The post_tags table contains the foreign keys of tags and posts.
Something like this (:
To retrieve a post and all its tags, you need to JOIN the tables in your queries:
This answer is adapted from How to store arrays in MySQL from StackOverflow.
Thank you for sharing the code sample.
But, I want to share multiple tags for single post.
On using the
INNER JOINclause it doesn't get the data if specific post have multipletag_idit only return the data for 3rd row according to the above image.
Can you see the mistake here?
You need to do the JOIN with the post_tags table (with both references, posts and tags) to work.
Yeah I know that,
I tried with the both with and without
post_tagtable, it was return 0 rows.This is the best solution on the topic. +1