SQL join based on column value

SQL Server Administration
, ,
1

I am trying to join two tables based on the condition.

Table1:
ID      Portfolio
3       Department
5       Laboratory

Table2:
DepartmentID   LaboratoryID
      3             5

How can I join ID from table 1 with DepartmentID and LaboratoryID with table 2?

I tried below and didn't work

INNER JOIN Table1 ON 
(SELECT ID FROM Table1 WHERE Portfolio = 'Department') = 
Table2.DepartmentID AND
(SELECT ID FROM Table1 WHERE Portfolio = 'Laboratory') =
Table2.LaboratoryID)
2

Not real clear what you are asking - or looking for...but I would start with table2:

Select ...
  From table2        t2
 Inner Join table1 t1_1 On t1_1.ID = t2.DepartmentID And t1_1.Portfolio = 'Department'
 Inner Join table1 t1_2 On t1_2.ID = t2.LaboratoryID And t1_2.Portfolio = 'Laboratory'