how To get Distinct records in sql server

I have two tables like that

First Table

RecordID(P)	Question_ID	Answer_ID	User_ID	DateCreated
1	1	1	2	2/2/2009
2	2	2	2	2/2/2009

Second Table(question_ID is just the column which is same in both and can be used for join)

ID(P)	Question_ID	File_attached	Date_created
1	1	FirstFile.Doc	4/4/2009
2	1	SecondFile.xls	6/6/2009
3	2	Third File	7/7/2001

Now I have web Form in Which i want to show the Records like that I want to show all the Records from the first table but the Last inserted record from the second table for a specific question just like below

What i Did

Select FT.Question_ID,FT.Answer_ID,FT.User_ID From First Table AS FT

INNER JOIN SECOND_TABLE as ST ON ST.Question_ID=FT.Question_ID Where Userid=@userID so as my Second_Table has two rows for Question one it shows two result set due to inner join I have checked left , right all joins

FOR QUESTION_ID=1 I have Two Recrods in the Second Table so it shows two record I need to show the one record for Question_ID=1 and it must join the last inserted record into table two for question_ID=1.

I have used the where clause with (select MAx(ID) from SecondTable) then it is finding the correct record but it omit the record for Question_ID=2 it just show the one record instead of two {one for question_ID=1 (with latest file inserted into the second table ) and one for the question_ID=2 whose file is just one so no problem.

please assists me in that query

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Danish Habib10y ago
I have solved the above query by Placing the Where Condition for Second table with the LEFT OUTER JOIN WHERE I AM JOINING THE LEFT TABLE this solve my issue . so if any one get struck into that issue place your where condition with the join where you are joining and want to get the latest record from the table so the query now becomes

Select FT.Question_ID,FT.Answer_ID,FT.User_ID From First Table AS FT
INNER JOIN SECOND_TABLE as ST ON ST.Question_ID=FT.Question_ID AND ID=(SELECT (Max(ID) from Second_Table) where FT.UserID=@userID

the yellow line is the Correction