Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ATG
Starting Member
35 Posts |
Posted - 2013-01-24 : 17:57:27
|
I need to join two tables. One is just a static table that contains a list of values. The second table varies in content. Table 1 has values:010000011000012000013000...991000Table 2 would be something like:012000, $150013000, $465022000, $45The problem I'm getting is that it only returns rows where Table 2 has a value.I need it to show like010000, null011000, null012000, $150...012100, null013000, $465etc...Whats the best way to go about doing this? I thought a left join would do it but it doesn't seem to be right.Here is what I have so far....select *from Table1LEFT JOIN Table2 on Table1.Company=Table2.Company and Table1.WBSCode=Table2.WBSCodewhere (Table2.Company=1 or Table2.Company is null) and (Table2.JobNum='132311.' or Table2.JobNum is null) or (Table2.Order='35' or Table2.Order is null) |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-24 : 18:24:41
|
You're correct in doing a left join, but it looks like you where clause is turning you left join into an inner join. Try something like thisselect *from Table1LEFT JOIN Table2 on Table1.Company=Table2.Company and Table1.WBSCode=Table2.WBSCodeand (Table2.Company=1 or Table2.JobNum='132311.' Table2.Order='35')JimEveryday I learn something that somebody else already knew |
|
|
ATG
Starting Member
35 Posts |
Posted - 2013-01-24 : 18:46:14
|
I had a feeling that was happening but didn't think to solve it via the join, but rather the where clause.The code below did it...select *from Table1LEFT JOIN Table2 on Table1.Company=Table2.Company and Table1.WBSCode=Table2.WBSCodeand ((Table2.Company=1 or Table2.Company is null) and (Table2.JobNum='132311.' or Table2.JobNum is null) and (Table2.Order='35' or Table2.Order is null))Once again, thanks alot! |
|
|
|
|
|