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)
);

@Daniel Vassallo I expect all the posts related to that specific tag to be displayed. — pops
– pops, Commented Aug 11, 2010 at 2:02
Get rid of the GROUP BY but otherwise the only issue I can see is that the data you think exists, doesn't — OMG Ponies
– OMG Ponies, Commented Aug 11, 2010 at 2:07
@OMG Ponies getting rid of GROUP BY will display duplicate entries of the same post. — pops
– pops, Commented Aug 11, 2010 at 2:11
Try LEFT JOIN to posts? But it should work. I recon it is a data issue. — Ryk
– Ryk, Commented Aug 11, 2010 at 2:14

Daniel Vassallo · Accepted Answer · 2010-08-11 14:55:20Z

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)
);

I caught that two so I changed GROUP BY tags.tag to GROUP BY posts_tags.posts_id

Collectives™ on Stack Overflow

MySQL query display problem

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related