Sql Query for retriving proper data based on join condition

• aniletc37

  SSC Rookie

  Points: 29

  More actions

  January 23, 2017 at 11:33 pm

  #401468

  Hi All,

  I have two tables Emp and Loc and pick the data from these two tables based on joining condition of empid on both the tables.After that i have to check the values with one lookup table Job and pick the proper Jname and jdesc for the respective codes.Here empcode,deptcode,loccode needs to be join with JID for getting the required details,If corresponding empcode,deptcode,loccode not found in job table it will populate as UNK and unknown in codes  and desc fields..In this case how to apply multiple joins on different tables.Any one can guide me.Please check below for more details.
  employee:

  Empid	FNAME	LNAME	EMPCODE	DEPTCODE
  1	A	G	10	100
  2	B	H	20	200
  3	C	I	30	300
  4	D	J	40	400
  5	E	K	50	500
  6	F	L	60	600
  7	X	Y	1000	2000

  location:

  EMPID	LOCNBR	LOCCODE
  1	22	32
  2	23	33
  3	24	34
  4	25	35
  5	26	36
  6	27	37
  8	28	38

  Job:

  JID	JNAME	JDESC
  10	AB	Test1
  20	BC	Test2
  30	CD	Test3
  40	DE	Test4
  50	EF	Test5
  100	VW	Test15
  200	XY	Test16
  300	ABC	Test17
  400	DEF	Test18
  500	GHI	Test19
  32	JK	Test8
  33	LM	Test9
  34	MN	Test10
  35	OP	Test11
  36	QR	Test12
  1000	HI	Test7
  2000	MNO	Test21
  38	TU	Test14
  7000	PQR	Test22
  8000	STU	Test23

  Output:

  Empid

  FNAME

  LNAME

  LOCNBR

  EMPCODE

  EMPJNAME

  EMPJDESC

  DEPTCODE

  DEPTJNAME

  DEPTJDESC

  LOCCODE

  LOCJNAME

  LOCJDESC

  1

  A

  G

  22

  10

  AB

  Test1

  100

  VW

  Test15

  32

  JK

  Test8

  2

  B

  H

  23

  20

  BC

  Test2

  200

  XY

  Test16

  33

  LM

  Test9

  3

  C

  I

  24

  30

  CD

  Test3

  300

  ABC

  Test17

  34

  MN

  Test10

  4

  D

  J

  25

  40

  DE

  Test4

  400

  DEF

  Test18

  35

  OP

  Test11

  5

  E

  K

  26

  50

  EF

  Test5

  500

  GHI

  Test19

  36

  QR

  Test12

  6

  F

  L

  27

  60

  UNK

  UNKNOWN

  600

  UNK

  UNKNOWN

  37

  UNK

  UNKNOWN

  Tried with below querybut not able to get proper solution:
  select t.empid,t.fname,t.lname,t.empcode,j1.jname as EMPJNAME,j1.jdesc as EMPJDESC from job j1 right join

  (SELECT a.empid,a.fname,a.lname,a.empcode,a.deptcode,b.locnbr,b.lcocode from emp a
  inner join loc b
  on a.empid=b.empid)as t on j1.empcode=t.jid

  Scripts for refrence:

  Create table emp(
  empid varchar(50),
  FNAME varchar(50),
  LNAME varchar(50),
  EMPCODE varchar(50),
  DEPTCODE varcharr(50));

  Create table loc(
  empid varchar(50),
  LOCNBR varchar(50),
  LOCCODE varchar(50));

  Create table job(
  Jid varchar(50),
  JNAME varchar(50),
  JDESC varchar(50));

  INSERT into emp VALUES (1, 'A', 'G', 10, 100);

  INSERT into emp VALUES (2, 'B', 'H', 20, 200);

  INSERT into emp VALUES (3, 'C', 'I', 30, 300);

  INSERT into emp VALUES (4, 'D', 'J', 40, 400);

  INSERT into emp VALUES (5, 'E', 'K', 50, 500);

  INSERT into emp VALUES (6, 'F', 'L', 60, 600);

  INSERT into emp VALUES (7, 'X', 'Y', 1000, 2000);

  --------------------------------------------------------

  INSERT into loc VALUES (1, 22, 32);

  INSERT into loc VALUES (2, 23, 33);

  INSERT into loc VALUES (3, 24, 34);

  INSERT into loc VALUES (4, 25, 35);

  INSERT into loc VALUES (5, 26, 36);

  INSERT into loc VALUES (6, 27, 37);

  INSERT into loc VALUES (8, 28, 38);

  ---------------------------------------------------------------

  INSERT into job values (10, 'AB', 'Test1');

  INSERT into job values (20, 'BC', 'Test2');

  INSERT into job values (30, 'CD', 'Test3');

  INSERT into job values (40, 'DE', 'Test4');

  INSERT into job values (50, 'EF', 'Test5');

  INSERT into job values (100, 'VW', 'Test15');

  INSERT into job values (200, 'XY', 'Test16');

  INSERT into job values (300, 'ABC', 'Test17');

  INSERT into job values (400, 'DEF', 'Test18');

  INSERT into job values (500, 'GHI', 'Test19');

  INSERT into job values (32, 'JK', 'Test8');

  INSERT into job values (33, 'LM', 'Test9');

  INSERT into job values (34, 'MN', 'Test10');

  INSERT into job values (35, 'OP', 'Test11');

  INSERT into job values (36, 'QR', 'Test12');

  INSERT into job values (1000, 'HI', 'Test7');

  INSERT into job values (2000, 'MNO', 'Test21');

  INSERT into job values (38, 'TU', 'Test14');

  INSERT into job values (7000, 'PQR', 'Test22');

  INSERT into job values (8000, 'STU', 'Test23');

• Thom A

  SSC Guru

  Points: 99128

  More actions

  January 24, 2017 at 2:34 am

  #1924542

  You can join on many tables, that's not a problem. So, you could easily have a query that looks like:
  SELECT E.Name,
       D.Department,
       ISNULL(J.Name, 'No Assigned Role') AS JobRole
FROM Employee E
    JOIN Department D ON E.DepartmentID = D.DeparmentID
    LEFT JOIN Job J ON E.EmployeeID = J.EmployeeID;

  This would JOIN the Employees table to both the Department and Job tables, however, if no job for an employee is found, it will still return the Employee (as it is a LEFT JOIN).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 24, 2017 at 2:49 am

  #1924546

  Great job on posting the sample data, but your query has several invalid object name errors in it.  If you could post a query that runs (even if it returns the wrong results), please, we should be able to show you where you're going wrong.

  John