Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Left join not working??
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ATG
Starting Member

USA
35 Posts

Posted - 01/24/2013 :  17:57:27  Show Profile  Reply with Quote
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:

010000
011000
012000
013000
.
.
.
991000


Table 2 would be something like:

012000, $150
013000, $465
022000, $45

The problem I'm getting is that it only returns rows where Table 2 has a value.

I need it to show like

010000, null
011000, null
012000, $150
...
012100, null
013000, $465

etc...

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 Table1
LEFT JOIN Table2 on Table1.Company=Table2.Company and Table1.WBSCode=Table2.WBSCode

where (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
Flowing Fount of Yak Knowledge

USA
2875 Posts

Posted - 01/24/2013 :  18:24:41  Show Profile  Reply with Quote
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 this

select *

from Table1
LEFT JOIN Table2 on Table1.Company=Table2.Company and Table1.WBSCode=Table2.WBSCode
and (Table2.Company=1 or Table2.JobNum='132311.' Table2.Order='35')

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

ATG
Starting Member

USA
35 Posts

Posted - 01/24/2013 :  18:46:14  Show Profile  Reply with Quote
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 Table1
LEFT JOIN Table2 on Table1.Company=Table2.Company and Table1.WBSCode=Table2.WBSCode
and ((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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000