CODEWITHSUNDEEP
mysqldatabase
Sharique Anwer
Sharique Anwer

Reputation: 125

Get unreplied message from MySQL

I have a table in mysql which is not properly structured. It was developed by a previous developer. Table structure in below:-

id (primary_key)
from_user_id (sender)
post_id (thread_id)
to_user_id (receiver)
Message (text_content)
datetime (mysql_timestamp)

lets say our inter user id (from_user_id) exist in a predefined array i.e. (4,182,193).

So i want to get only those rows on which last reply was made by user that is not exist in the above array.

I have made a query but that is returning only a single row.

Can you all please help me on that?

My query is:-

SELECT * 
from table 
WHERE id IN (
  SELECT MAX(id) 
  FROM table 
  where from_user_id not in (4,182,193)
)
GROUP BY post_id;

Upvotes: 0

Views: 79

Answers (2)

Lajos Arpad
Lajos Arpad

Reputation: 76464

Select the last id for any posts and then use the resulting ids as filters:

select *
from table t1
where from_user_id not in (4,182,193)
  and id in
(
select max(t2.id)
from table t2
group by t2.post_id
)

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

for get all the id not in your array you should select all the ids not only the max and use where id not in .. too.

for a comparision you need a column name 

    SELECT * 
    from table 
    WHERE id IN (
      SELECT id
      FROM table 
      where from_user_id where id  not in (4,182,193)
    )

Upvotes: 0

Related Questions