MySQL query display problem.

Question

I have this query that should display all posts related to a specific tag but for some reason it will only display one result instead of all the results can some one help me fix this problem?

Here is MySQL code.

"SELECT users.*, users_posts.*, tags.*, posts_tags.*, 
FROM users
INNER JOIN users_posts ON users_posts.user_id = users.user_id 
INNER JOIN posts_tags ON users_posts.id = posts_tags.posts_id
INNER JOIN tags ON posts_tags.tag_id = tags.id
WHERE tags.tag = '" . $tag_id . "'
GROUP BY tags.tag"

Here is my MySQL table.

CREATE TABLE posts_tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag_id INT UNSIGNED NOT NULL,
users_posts_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE users_posts (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
content TEXT NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
pass CHAR(40) NOT NULL,
PRIMARY KEY (user_id)
);

Answer 1

I added some test data to your tables:

INSERT INTO tags VALUES (1, 'mysql');
INSERT INTO tags VALUES (2, 'javascript');
INSERT INTO tags VALUES (3, 'c++');

INSERT INTO users VALUES (1, 'user a', 'pass');
INSERT INTO users VALUES (2, 'user b', 'pass');
INSERT INTO users VALUES (3, 'user c', 'pass');

INSERT INTO users_posts VALUES (1, 1, 'some content 1');
INSERT INTO users_posts VALUES (2, 1, 'some content 2');
INSERT INTO users_posts VALUES (3, 2, 'some content 3');
INSERT INTO users_posts VALUES (4, 2, 'some content 4');
INSERT INTO users_posts VALUES (5, 2, 'some content 5');
INSERT INTO users_posts VALUES (6, 3, 'some content 6');

INSERT INTO posts_tags VALUES (1, 1, 1);
INSERT INTO posts_tags VALUES (2, 2, 1);
INSERT INTO posts_tags VALUES (3, 1, 2);
INSERT INTO posts_tags VALUES (4, 3, 2);
INSERT INTO posts_tags VALUES (5, 2, 3);
INSERT INTO posts_tags VALUES (6, 2, 4);
INSERT INTO posts_tags VALUES (7, 3, 4);
INSERT INTO posts_tags VALUES (8, 1, 5);
INSERT INTO posts_tags VALUES (9, 2, 6);
INSERT INTO posts_tags VALUES (10, 3, 6);

Then removing the GROUP BY:

SELECT      *
FROM        users
INNER JOIN  users_posts ON users_posts.user_id = users.user_id
INNER JOIN  posts_tags ON users_posts.id = posts_tags. users_posts_id
INNER JOIN  tags ON posts_tags.tag_id = tags.id
WHERE       tags.tag = 'mysql';

Returns:

+---------+--------+------+----+---------+----------------+----+--------+----------------+----+-------+
| user_id | name   | pass | id | user_id | content        | id | tag_id | users_posts_id | id | tag   |
+---------+--------+------+----+---------+----------------+----+--------+----------------+----+-------+
|       1 | user a | pass |  1 |       1 | some content 1 |  1 |      1 |              1 |  1 | mysql |
|       1 | user a | pass |  2 |       1 | some content 2 |  3 |      1 |              2 |  1 | mysql |
|       2 | user b | pass |  5 |       2 | some content 5 |  8 |      1 |              5 |  1 | mysql |
+---------+--------+------+----+---------+----------------+----+--------+----------------+----+-------+
3 rows in set (0.00 sec)

It's just one row per post, as long as the posts are not tagged with the same tag more than once. In fact, to prevent this from happening, you may want to consider eliminating the surrogate key in posts_tags and use a composite primary key on (tag_id, users_posts_id):

CREATE TABLE posts_tags (
   tag_id INT UNSIGNED NOT NULL,
   users_posts_id INT UNSIGNED NOT NULL,
   PRIMARY KEY (tag_id, users_posts_id)
);