Multiple Table QueryLocked
I am very new to SQL. I have been a Network architect/engineer for 23 years and was recently dropped into DBA/Developer mode. I am learning on the fly – and very quickly (I think), but I’m really stuck on a little problem.
I have 3 tables, each are related by Case Number (field names slightly vary). I am using all of the fields from table2, one of the fields from table3, and running the query mostly from data in table1 and table3. The result I am looking for should show all open tasks (with no duplicate records) equal to a specified task ID from table1 and, based on the Case_No relationship in the 3 tables, I am looking for table3 to provide a current amount due, if there is one. What I am getting is all open cases = to the specified task ID that have a current amount due. What I had hoped for was all open tasks = to the specified task ID whether there is a current amount due or not. The purpose of using CountAmount in Table3 is only to show the amount currently due if there is an amount identified in the table.
Here is what I have in the statement:
SELECT DISTINCT Caseload_Realignment.dbo.Teamcase.Case_No,
Open_Tasks INNER JOIN
Caseload_Realignment.dbo.Teamcase ON Open_Tasks.[Case Number] = Caseload_Realignment.dbo.Teamcase.Case_No
RIGHT OUTER JOIN
(Open_Tasks.[Task Id] = ‘en021’) AND
([1257data].dbo.CSE1257_200805.LineNbr = 24)AND
([1257data].dbo.CSE1257_200805.SubLineChar = ‘a’) AND
([1257data].dbo.CSE1257_200805.LineColumn = ‘T’)
If I remove the 1257data table I get all open tasks = to the specified task ID, but I get no current amount due. I’m really having a struggle with this and appreciate any suggestions.
Please tell me where I’m going wrong (my brain is starting to hurt from all the strain -haha).