Forum Discussion

Mohamed Talha's avatar
Mohamed Talha
Copper Contributor
Oct 12, 2017

Get Query from two Tables

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

Sort By
  • 耕志 馮's avatar
    耕志 馮
    Copper Contributor
    Oct 18, 2017

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

Resources