| Author |
Topic |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2009-03-16 : 13:08:57
|
| I need to extract data from three tables but think I have the join wrong. Here is what I have:from (hosp_nmrc LEFT JOIN hosp_rpt on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM) and (state_codes RIGHT JOIN hosp_rpt on state_codes.Ssa_State_Cd = hosp_nmrc.state_code)How are joins on multiple tables coded? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-16 : 13:13:06
|
| from (hosp_nmrc LEFT JOIN hosp_rpt on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM) LEFT JOIN state_codes on state_codes.Ssa_State_Cd = hosp_nmrc.state_code |
 |
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2009-03-16 : 13:43:17
|
quote:
Originally posted by visakh16
from (hosp_nmrc LEFT JOIN hosp_rpt on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM) LEFT JOIN state_codes on state_codes.Ssa_State_Cd = hosp_nmrc.state_code
Thank you, Vikash!If I want all of the records in the state codes table, should I use a left join or a right join? Why? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-16 : 13:46:45
|
if you want all records in state code table regardless of other condition, you ned like thisfrom state_codes LEFT JOIN hosp_nmrc on state_codes.Ssa_State_Cd = hosp_nmrc.state_codeLEFT JOIN hosp_rpt on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2009-03-16 : 13:53:53
|
quote:
Originally posted by visakh16
if you want all records in state code table regardless of other condition, you ned like thisfrom state_codes LEFT JOIN hosp_nmrc on state_codes.Ssa_State_Cd = hosp_nmrc.state_codeLEFT JOIN hosp_rpt on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM
Why does that work? Sorry for the all the questions! I am new to SQL! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-16 : 13:59:01
|
| the table from which you want should be first in query (base table) folowed by left joins. this will make sure all records from left potion of join are included (base table) and matching values if any will be returned from right side of joins. if no matches, then NULL will be returned. suggest you to read about left,right,inner...joins from books online to understand the complete difference |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2009-03-16 : 14:04:55
|
quote:
Originally posted by visakh16
the table from which you want should be first in query (base table) folowed by left joins. this will make sure all records from left potion of join are included (base table) and matching values if any will be returned from right side of joins. if no matches, then NULL will be returned. suggest you to read about left,right,inner...joins from books online to understand the complete difference
Great, thank you for the explanation. One other thing I am still getting used to is using on-line books as a resource!Thanks again,JB |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-16 : 14:08:48
|
| have you not installed sql server books online? |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-03-16 : 17:14:56
|
| You get the same along with the installation. A help menu which is pretty much the same as what you have online. You might have not installed it. |
|
|
|
|
Joins