#1241 - Operand should contain 1 column(s) MYSQL

Question

I am trying to get data from the database like that, but i have this error how can i fix?

SELECT post.text,users.name,users.surname,users.profile_id,post.post_id,comments.text as comment, 
(SELECT user.name, user.surname FROM users user WHERE profile_id = comments.profile_id) as name_comment 
  FROM post
    INNER JOIN users ON users.profile_id = post.profile_id
    INNER JOIN comments ON comments.profile_post = post.post_id

Answer 1

Simply JOIN to the users table twice

SELECT
    post.text,
    userpost.name,
    userpost.surname,
    userpost.profile_id,
    post.post_id,
    comments.text as comment,
    usercomment.name, usercomment.surname -- this
  FROM post
    INNER JOIN users userpost ON userpost.profile_id = post.profile_id
    INNER JOIN comments ON comments.profile_post = post.post_id
    INNER JOIN users usercomment ON comments.profile_id = usercomment.profile_id

Answer 2

Your subquery:

(SELECT user.name, user.surname 
FROM users user
WHERE profile_id = comments.profile_id) as name_comment 

has 2 fields instead one

You can:

1. Use 2 distinct subquery to get user.name and user.surname;

2. concatenate the two information so you have one output field;

3. Why you use subquery when you have joined your users table in the main query (with the same condition)

Answer 3

try this

 SELECT post.text,users.name,users.surname,users.profile_id,post.post_id,comments.text as comment 
FROM post
INNER JOIN users ON users.profile_id = post.profile_id
INNER JOIN comments ON comments.profile_post = post.post_id
WHERE profile_id = comments.profile_id