Get Query from two Tables

Hi Mohamed:

Frankly speaking, build a view may inappropriate solution against your requirement. You can create a function in database or in your program code instead.

Please try the script below:

select a.projectid,a.resource1,b.accountno as account1,
                            a.resource2,c.accountno as account2,
                            a.resource3,d.accountno as account3,
                            a.resource2,e.accountno as account4
from table1 as a left join table2 b on a.resource1 = b.resourceid
left join table2 c on a.resource2 = c.resourceid
left join table2 d on a.resource3 = d.resourceid
left join table2 e on a.resource4 = e.resourceid
where a.projectid = '1'

Note that you can use isnull() function to convert the null value to other one readable. 

Hope this can help you.....