SQL join based on column value-CodePudding

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)

CodePudding user response：

..join table1 twice, once for departmentid and once for laboratoryid :

select dl.departmentid, d.portfolio as depname, dl.laboratoryid, l.portfolio as labname
from table2 as dl
join table1 as d on dl.departmentid = d.id
join table1 as l on dl.laboratoryid = l.id;

CodePudding user response：

SELECT Table1.ID
     , Table1.Portfolio
     , department.DepartmentID
     , laboratory.LaboratoryID
FROM   Table1
 LEFT
  JOIN Table2 AS department
    ON department.DepartmentID = Table1.ID
   AND Table1.Portfolio = 'Department'
 LEFT
  JOIN Table2 AS laboratory
    ON laboratory.LaboratoryID = Table1.ID
   AND Table1.Portfolio = 'Laboratory'
;