CODEWITHSUNDEEP
sql-serverjoin
lms
lms

Reputation: 93

SQL Server join with no common columns

I need a help with below scenario

Table A is a hierarchy table. Table B is a transaction table. I need to alter the table b to bring the deptdesc, classdesc,subclassdesc from table A. How should I join the two tables in this scenario to get the respective description fields in table b as shown in result. Appreciate your help

Table A:

DeptID ClassID SubClassID DeptDesc ClassDesc SubClassDesc
001    001     001        Math     A         A1
001    001     002        Math     A         A2
001    002     001        Math     B         B1
002    001     001        Eng      A         A1        
002    002     001        Eng      B         B1
002    002     002        Eng      B         B2

Table B

 deptid Classid subclassid updatedate Status
 001    002     002        01-Mar-16  Yes
 002    001     001        02-may-16  No`

Table B results

 deptid Classid subclassid updatedate Status Deptdesc  ClassDesc Subclassdesc
  001    002     002        01-Mar-16  Yes    Math      B         B1
  002    001     001        02-may-16  No     Eng       A         A1`

Upvotes: 0

Views: 54

Answers (1)

αNerd
αNerd

Reputation: 528

With Inner Join

SELECT TableB.deptID, TableA.ClassID, TableB.subClassID, updatedate, Status, DeptDesc, 
ClassDesc, SubClassDesc FROM TableB
INNER JOIN TableA
ON TableA.DeptId = TableB.deptId 
AND TableA.ClassId = TableB.classId 
AND TableA.SubclassId = TableB.subclassId

you will have the result:

Table B Result
deptid Classid subclassid updatedate Status Deptdesc  ClassDesc Subclassdesc
  002    001     001        02-may-16  No     Eng       A         A1

With Left Join instead of Inner Join you should have as result:

Table B Result
deptid Classid subclassid updatedate Status Deptdesc  ClassDesc Subclassdesc
  001    NULL    002        01-Mar-16  Yes    NULL      NULL      NULL
  002    001     001        02-may-16  No     Eng       A         A1

But you can not have:

Table B Result
deptid Classid subclassid updatedate Status Deptdesc  ClassDesc Subclassdesc
  001    002     002        01-Mar-16  Yes    Math      B         B1
  002    001     001        02-may-16  No     Eng       A         A1

because you do not have DeptId = 1, ClassId = 2, SubClassId = 2 in TableA.

Upvotes: 1

Related Questions