How to show multiple record if has top 1 Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date &lt; 2019-12-05. 

Table 1

Key 2          Name



78     Test 1 
222    Test 2            
824    Test 3  
1980   Test 4
3747   Test 5


Please see image for table 2

select top 1 (md2.table2) from table2 md2
where md2.EFFECTIVEDATE &lt; '2019-12-05'
and md2.key2 in (select key2 from table1)
order by EFFECTIVEDATE desc


my query only getting one record i want to get all the record on key 2
Top is only giving you one record because you're asking for the top '1' row.  If you want all, just change it to: 

select (md2.table2) from table2 md2
where md2.EFFECTIVEDATE &lt; '2019-12-05'
and md2.key2 in (select key2 from table1)
order by EFFECTIVEDATE desc