Get Query from two Tables

Highlighted
Occasional Visitor

Hi..

i need to Create a View that query data from two tables as follows

Table1 as Project (its contain colums ProjectID, Resource1,Resource2, Resource3, Resource4 etc)

Table2 as ResourceTable (it contain column ResourceID, AccountNO etc)

 

i need to query the Acccount No for four resource based on ProjectID

like

ProjectID, AccountNO for Resource1, AccountNO for Resource2, AccountNO for Resource3, AccountNO for Resource4

Can anyone help me to get above query..

Thanks

1 Reply
Highlighted

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.....