CODEWITHSUNDEEP
mysqlsqlselectsubquerygroup-concat
compguy24
compguy24

Reputation: 957

mysql subquery returns multiple rows

My problem is in the first subquery select. I am being told that I am returning multiple rows.

$sql = "SELECT
      messages.message_id
    , messages.sent_timestamp
    , messages.content
    , messages.subject
    , users.user_name
    , (SELECT thread_participants.user_id 
FROM thread_participants 
WHERE thread_participants.user_id !=".$user_id.") as thread_participants
    , (SELECT message_read_state.readDate
       FROM message_read_state
       WHERE message_read_state.message_id = messages.message_id
        and message_read_state.user_id =". $user_id.") as ReadState
    FROM (messages INNER JOIN users ON messages.sender_user_id = users.user_id
    INNER JOIN thread_participants tp ON tp.thread_id = messages.thread_id)
    WHERE (((messages.thread_id)=".$thread_id."))
    ORDER BY messages.sent_timestamp DESC";

Upvotes: 2

Views: 6961

Answers (3)

compguy24
compguy24

Reputation: 957

One way I found of doing it:

$sql = "SELECT
      messages.message_id
    , messages.sent_timestamp
    , messages.content
    , messages.subject
    , users.user_name
    , tp.user_id as thread_participants
    , (SELECT users.user_name FROM users WHERE users.user_id = thread_participants && users.user_id != messages.sender_user_id) as member_names
    , (SELECT message_read_state.readDate
       FROM message_read_state
       WHERE message_read_state.message_id = messages.message_id
        and message_read_state.user_id =". $user_id.") as ReadState
    FROM (messages INNER JOIN users ON messages.sender_user_id = users.user_id
    INNER JOIN thread_participants tp ON tp.thread_id = messages.thread_id)
    WHERE (((messages.thread_id)=".$thread_id."))
    ORDER BY messages.sent_timestamp DESC";

Upvotes: 0

Irek Kubicki
Irek Kubicki

Reputation: 57

In most cases, in mysql, subqueries are less optimal than using separate queries. In that cases it's better to use data from previous query (like collection of ids) and use it in another query. Make some tests with your data. As usual amount of data makes the difference ;)

Also spliting queries may help you to cache the data in better way.

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

You can use GROUP_CONCAT() function to get all userIds in single field

Try this: 

$SQL = "SELECT
      messages.message_id
    , messages.sent_timestamp
    , messages.content
    , messages.subject
    , users.user_name
    , (SELECT GROUP_CONCAT(thread_participants.user_id) 
FROM thread_participants 
WHERE thread_participants.user_id !=".$user_id.") as thread_participants
    , (SELECT message_read_state.readDate
       FROM message_read_state
       WHERE message_read_state.message_id = messages.message_id
        and message_read_state.user_id =". $user_id.") as ReadState
    FROM (messages INNER JOIN users ON messages.sender_user_id = users.user_id
    INNER JOIN thread_participants tp ON tp.thread_id = messages.thread_id)
    WHERE (((messages.thread_id)=".$thread_id."))
    ORDER BY messages.sent_timestamp DESC";

Upvotes: 3

Related Questions