Good day! There is not much experience with sql, but I know the basics. Practice has shown that the fundamentals are not enough and I cannot find a solution (maybe I was looking badly, maybe there are not enough convolutions ...). The essence of the question is the following: there is the formation of the sql query:

$sql = "select usr.id, usr.$this->attributeNameUser as $this->attributeNameUser, msg.cnt as cnt_mess "; $sql .= "from $this->userTableName as usr "; $sql .= "inner join "; $sql .= "(select from_id, count(id) as cnt from $table_name where status = 1 and whom_id = :user_id GROUP by from_id) as msg "; $sql .= "ON msg.from_id = usr.id "; $sql .= " where usr.id != :user_id ";

Two tables are used here: user and message. This query returns the number of new messages from users (status = 1). If you remove this condition - it will return all the messages to me as new ... Question: Is it possible to correct the request that he would take users with whom I have a correspondence, and in the cnt property I entered values ​​with status 1 ??

  • This is a request from the message implementation module for the yii2 framework, so it’s extremely difficult to break up several different requests and generate data ... You need to pick the module itself, but you don’t want to do it ... - Oleg Shleif
  • Everything is not so simple, if the decision were so simple - I would have done it myself)) But thanks for the help! The bottom line is that if I put the left join, it will seem to me ALL the users, and not the ones with whom I am corresponding ... - Oleg Shleif
  • How to develop a thought: If there is status = 1, then the number of new messages from the user is returned to me ... If there is no status = 1, then all users with whom I corresponded with the total number of messages are returned to me! How to connect so that all users would be transferred and only messages from them were considered with status = 1 ??? - Oleg Shleif
  • You are just a genius! You just helped me incredibly !! Thank you very much!!! - Oleg Shleif
  • Mike, could you make your comment a response? - Oleg Shleif

1 answer 1

To leave all the records returned by the subquery, you should remove the selection condition status = 1 and count the number of records with status 1 using sum(case when status=1 then 1 else 0 end) or if the status can only take two values ​​0 and 1 is just a sum(status) .