mysql error: #1242 - Subquery returns more than 1 row

[final60]

Hi

Im receiving this error using the following mysql statement:

SELECT 
posts.title,
posts.content,
posts.date,
(SELECT user.username FROM urbex_users AS user WHERE user.user_id = posts.user_id) AS username,
(SELECT count(reply_id) FROM urbex_forum_replies AS rep WHERE rep.post_id = posts.post_id)AS replies,
cat.category_name,
posts.sticky,
posts.post_id,
is_read.post_id,
(SELECT is_read.is_read FROM urbex_forum_posts_is_read AS is_read WHERE posts.post_id = is_read.post_id AND is_read.user_id = 14) AS is_read,
posts.user_id


FROM
urbex_forum_posts AS posts,
urbex_forum_categories AS cat,
urbex_forum_posts_is_read AS is_read,
urbex_users AS users

WHERE
posts.category_id = cat.category_id AND
posts.post_id = is_read.post_id AND
posts.user_id = is_read.user_id AND
users.user_id = posts.user_id AND
posts.sticky = 0 AND
cat.category_id = 1

ORDER BY 

posts.date DESC

 

I checked the tables and as far as I can see there are no duplications anywhere. post_id is the pk for posts table which would make everything all posts unique in both the posts table and the is_read one?

 

When a new post is created a new entry is made in both the posts table and the is_read table.

The query works fine when I remove the is_read sub query. But I need it to tell the user when a new post or reply has been made.

[final60]

for some reason when i empty the tables and start creating new posts it displays them properly until I create say the 8th or 9th post in that category and then it shows the duplicate error. but im not sure how it would duplicate if the post_id's are all unique whihch keeps each row unique in the is_read table which consists of post_id, user_id ,is_read (0/1).

[fenway]

Well, you have 3 subqueries -- which one is causing the error?  Switch to COUNT(*) and you'll see.