CODEWITHSUNDEEP
mysqlsqltimestamp
Sourav
Sourav

Reputation: 17520

SQL based on Timestamp Difference calculation ON Multi table with multi condition - MySQL

Table structure and sample data

Wall_Update

   [INT         VARCHAR       VARCHAR         TIMESTAMP               TinyText]   
   pid [pk] |  CreatedBy  |   RepliedBy   |     Time               |     Text    

   1        |   sourav    |     sachin    |   2011-04-09 21:26:01  |      Hi

Friends

  UID  |    fUID

sourav |   sachin  
sachin |   sourav

I need to get PID for all those posts which was created by SOURAV or a friend of SOURAV and the Timeshould be atmost 3days old !

So i tried this 2 queries [and both fails]

select pid,`Text`,CreatedBy,Time,RepliedBy from WALL_UPDATE where CreatedBy in 
  (select FUID from friends where UID='sourav') or CreatedBy='sourav' and 
    TIMESTAMPDIFF (DAY , Time,CURRENT_TIMESTAMP)<=3 order by PID desc

SELECT WU.pid,WU.Text,MN.NameF,WU.Time,WU.CreatedBy FROM wall_update WU,main MN WHERE
  WU.CreatedBy in (select FUID from FRIENDS where UID='sourav') or
  WU.CreatedBy='sourav' and MN.uid=WU.createdBy and 
     TIMESTAMPDIFF (DAY , WU.time,CURRENT_TIMESTAMP ) <3 group by WU.pid

[both returns all PID created by SOURAV or a friend of SOURAV]

Upvotes: 1

Views: 284

Answers (1)

Gowri
Gowri

Reputation: 16835

try this

select * from WALL_UPDATE where (CreatedBy IN (select fuid from FRIENDS where uid ='sourav') or CreatedBy='sourav') and TIMESTAMPDIFF (DAY ,Time,now()) <=3
order by pid desc

use primary id to refer a friend user not name ,This will create dublicate

Upvotes: 1

Related Questions