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