August 30, 2008 03:26am pdt

1. chapmandew 1,914,331
2. angelIII 1,539,717
3. aneeshatti... 555,115
4. mark_wills 551,831
5. ScottPletc... 386,761
6. ee_rlee 349,324
7. acperkins 283,802
8. dtodd 278,051
9. EugeneZ 267,414
10. BrandonG... 170,092
11. Thomasian 153,038
12. cs97jjm3 150,650
13. appari 140,448
14. matthews... 138,203
15. momi_sa... 134,935
16. dbaSQL 126,394
17. brejk 122,990
18. rickchild 121,425
19. ptjcb 119,637
20. dqmq 113,074
21. dportas 112,530
22. bhess1 112,305
23. nmcdermaid 107,818
24. MohammedU 106,975
25. asvforce 103,642

1. angelIII 10,496,130
2. aneeshatti... 4,612,994
3. acperkins 4,272,262
4. ScottPletc... 4,200,813
5. arbert 2,245,195
6. Lowfatsp... 2,165,176
7. chapmandew 1,914,331
8. EugeneZ 1,543,576
9. rafrancisco 1,460,132
10. imran_fast 1,458,963
11. Hilaire 1,221,775
12. Nightman 1,143,404
13. nmcdermaid 1,092,402
14. Mr_Peer... 1,087,815
15. ptjcb 1,053,496
16. bhess1 1,049,297
17. dtodd 864,394
18. Sirees 774,152
19. adathelad 766,312
20. appari 727,685
21. BriCrowe 722,959
22. amit_g 683,229
23. matthews... 660,535
24. ShogunW... 619,598
25. Kevin3NF 590,766

07.07.2008 at 02:53PM PDT, ID: 23544925

	[x] Attachment Details

LEFT JOIN not working as expected - SQL Server 2000.

Asked by dbbishop in MS SQL Server, SQL Query Syntax

I have the code below. I happen to know that in the table WorkOrders I have one workorder that has three rows, one with a WorkorderStatusCode of 'O', 'R' and 'C' respectively. In the table AccessWorkorders there is only an 'R' and 'C' record. I would think the query below would provide me with the 'O' record from the first table, but it is not returning any data. What am I overlooking here?Start Free Trial

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

           
           
             select s.* 
from dbo.WorkOrders s
left outer join dbo.AccessWorkorders a
on s.WorkorderNumber = a.[WO Number] 
and cast(convert(char(10), s.WorkorderEntryDate, 120) as smalldatetime) = a.[Date Entered]
and s.PtnrAccountNumber = a.[Partner Account] 
AND s.ResidentName = a.[Resident Name] 
AND s.HomePhoneNumber = a. [Home TN]
and s.BusinessPhoneNumber = a.[Business TN] 
and s.AccountNumber = a.[BTN] 
and s.WorkorderTypeCode = a.[Work Order Type] 
and coalesce(s.CampaignCode, '') = coalesce(a.[Campaign], '') 
and s.WorkorderStatusCode = a.[Work Order Status] 
and s.ScCvvActIndicator = a.[Equipment Flag] 
and coalesce(s.WorkorderCompletionDate, '1900-01-01') = coalesce(a.[Completion Date], '1900-01-01')
and s.WorkorderReasonCode = a.[Work Order Reason 1]
where a.[wo number] is null
and a.[wo number] not like '%_1000_%' 
and cast(convert(char(10), s.WorkorderEntryDate, 120) as smalldatetime) = '2007-12-07'

           
         

Keywords: LEFT JOIN not working as expected - …

	Title
1	SQL JOIN adding rows to altered query
2	Multi-table SQl Query - not quite a joi…
3	Update inner join query help
4	SQL update specific rows
5	Java Database Join Problems
6	To Join or Not To Join

Loading Advertisement...

20080716-EE-VQP-32 / EE_QW_EXPERT_20070906

LEFT JOIN not working as expected - SQL Server 2000.

Asked by dbbishop in MS SQL Server, SQL Query Syntax

About this solution