SQL SERVER – GRAB RECORD FROM THE TABLE BASED ON COLUMN ID

I have a db with records like the following structure in the table;

id,msg,date,fid 
19 ,"hello","2012-02",0
20 ,"hello","2012-03",19

​x
 
id,msg,date,fid 19 ,"hello","2012-02",020 ,"hello","2012-03",19​

I have and entire db set up like this where the fid in some cases matches the id of another record. In that case id like to return one record with 2 different dates such as ;

19 ,"hello","2012-02",0,"2012-03"

 
19 ,"hello","2012-02",0,"2012-03"​

I was thinking a self join but got hung up

Answer

Yes self join is one way

   Select distinct t.id,t.msg,t.date, t1.date
   From table t join (Select fid, date from  
   Table) t1 on
    t.id =t1.fid

 
   Select distinct t.id,t.msg,t.date, t1.date   From table t join (Select fid, date from     Table) t1 on    t.id =t1.fid ​

Advertisement

Answer