SQL join based on column value

• learner0

  SSC Enthusiast

  Points: 189

  More actions

  September 26, 2021 at 8:44 am

  #3933153

  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)

  • This topic was modified 3 years, 10 months ago by learner0.

• frederico_fonseca

  SSCoach

  Points: 16245

  More actions

  September 26, 2021 at 11:01 am

  #3933164

  not enough information to tell you what is the correct way to do for your particular query but the following should point you on the right direction.

  select *
  from table2
  INNER JOIN Table1 port
   ON port.Portfolio = 'Department'
  and port.ID = Table2.DepartmentID
  inner join table1 lab
   ON port.Portfolio = 'Laboratory'
  and port.ID = Table2.LaboratoryID

  as we don't know what you trying to do neither the data from the tables and expected output what I gave may not be what you need.