How do I join for tables together?
-
Hi, I have to join four tables together and am having trouble. My query as it stands is returning data, but it's not the data I need. I do not think I have all four tables joined together correctly. How would I join the tables properly? Thank you. Justin I need to join the CXADMIN.RO_FAILURE_DTL RF, CXADMIN.RO_HIST RH, saadmin.sa_repair_part@elgsad rp, and saadmin.sa_code_group_task_dtl @ELGSAD cg tables together. Here is my query so far:
SELECT distinct RF.REPAIR_ORD, RH.RECV_UNIT, RH.RECV_SERIAL_NBR, rf.created_date, RP.FAULT_CODE, RP.REPAIR_ACTION_CODE, cg.task_code
FROM CXADMIN.RO_FAILURE_DTL RF, CXADMIN.RO_HIST RH, saadmin.sa_repair_part@elgsad rp, saadmin.sa_code_group_task_dtl @ELGSAD cg
WHERE RF.REPAIR_ORD = RH.REPAIR_ORD and Rp.REPAIR_ORD = cg.REPAIR_ORD
AND RF.FAILURE_CODE ='DISK'AND RH.CURR_FACILITY_ID ='23' AND RF.CREATED_DATE >'1-JUN-2010' AND RF.CREATED_DATE < '1-dec-2010'
AND (CG.TASK_CODE ='PHMD' OR CG.TASK_CODE ='PHSN' OR CG.TASK_CODE ='CHMD' OR CG.TASK_CODE ='CHSN') -
Hi, I have to join four tables together and am having trouble. My query as it stands is returning data, but it's not the data I need. I do not think I have all four tables joined together correctly. How would I join the tables properly? Thank you. Justin I need to join the CXADMIN.RO_FAILURE_DTL RF, CXADMIN.RO_HIST RH, saadmin.sa_repair_part@elgsad rp, and saadmin.sa_code_group_task_dtl @ELGSAD cg tables together. Here is my query so far:
SELECT distinct RF.REPAIR_ORD, RH.RECV_UNIT, RH.RECV_SERIAL_NBR, rf.created_date, RP.FAULT_CODE, RP.REPAIR_ACTION_CODE, cg.task_code
FROM CXADMIN.RO_FAILURE_DTL RF, CXADMIN.RO_HIST RH, saadmin.sa_repair_part@elgsad rp, saadmin.sa_code_group_task_dtl @ELGSAD cg
WHERE RF.REPAIR_ORD = RH.REPAIR_ORD and Rp.REPAIR_ORD = cg.REPAIR_ORD
AND RF.FAILURE_CODE ='DISK'AND RH.CURR_FACILITY_ID ='23' AND RF.CREATED_DATE >'1-JUN-2010' AND RF.CREATED_DATE < '1-dec-2010'
AND (CG.TASK_CODE ='PHMD' OR CG.TASK_CODE ='PHSN' OR CG.TASK_CODE ='CHMD' OR CG.TASK_CODE ='CHSN')For joining tables, SQL offers a very specific keyword, it is documented here[^]. You would need three of those for joining four tables together. Warning: There are some variations, controlling what to do when rows in one table don't match rows in the other tables. Here is an article that might help you on the subject: Visual Representation of SQL Joins[^] :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Hi, I have to join four tables together and am having trouble. My query as it stands is returning data, but it's not the data I need. I do not think I have all four tables joined together correctly. How would I join the tables properly? Thank you. Justin I need to join the CXADMIN.RO_FAILURE_DTL RF, CXADMIN.RO_HIST RH, saadmin.sa_repair_part@elgsad rp, and saadmin.sa_code_group_task_dtl @ELGSAD cg tables together. Here is my query so far:
SELECT distinct RF.REPAIR_ORD, RH.RECV_UNIT, RH.RECV_SERIAL_NBR, rf.created_date, RP.FAULT_CODE, RP.REPAIR_ACTION_CODE, cg.task_code
FROM CXADMIN.RO_FAILURE_DTL RF, CXADMIN.RO_HIST RH, saadmin.sa_repair_part@elgsad rp, saadmin.sa_code_group_task_dtl @ELGSAD cg
WHERE RF.REPAIR_ORD = RH.REPAIR_ORD and Rp.REPAIR_ORD = cg.REPAIR_ORD
AND RF.FAILURE_CODE ='DISK'AND RH.CURR_FACILITY_ID ='23' AND RF.CREATED_DATE >'1-JUN-2010' AND RF.CREATED_DATE < '1-dec-2010'
AND (CG.TASK_CODE ='PHMD' OR CG.TASK_CODE ='PHSN' OR CG.TASK_CODE ='CHMD' OR CG.TASK_CODE ='CHSN')Your code is using an implicit equijoin and a cartesianjoin.
WHERE RF.REPAIR_ORD = RH.REPAIR_ORD and Rp.REPAIR_ORD = cg.REPAIR_ORD
For simplicity, let me assume the actual table names are RF, RH, RP and CG. RH and RH are joined and RP and CG are joined but RF is not joined to RP or CG; and also RH is not joined to RP and CG. So the end result is a cartesian product of two equijoins (RF-RH X RP-CG). I would suggest using an explicit join. From your code, I assume that REPAIR_ORD is common to all the tables. So my code would look like
SELECT .....
FROM
RF INNER JOIN RH
ON RF.REPAIR_ORD=RH.REPAIR_ORDINNER JOIN RP
ON RH.REPAIR_ORD=RP.REPAIR_ORDINNER JOIN CG
ON RP.REPAIR_ORD=CG.REPAIRD_ORDWHERE ....
I am using inner joins but depending on what you need, you may find left joins or right joins more suitable.