Question

Locked

Multiple Table Query

By llabbitt ·
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,
Caseload_Realignment.dbo.Teamcase.Ncp_Name,
Caseload_Realignment.dbo.Teamcase.Off_Name,
Caseload_Realignment.dbo.Teamcase.Team_Name,
Caseload_Realignment.dbo.Teamcase.Case_Worker,
Caseload_Realignment.dbo.Teamcase.[Current_Date],
[1257data].dbo.CSE1257_200805.CountAmount

FROM
Open_Tasks INNER JOIN
Caseload_Realignment.dbo.Teamcase ON Open_Tasks.[Case Number] = Caseload_Realignment.dbo.Teamcase.Case_No
RIGHT OUTER JOIN
[1257data].dbo.CSE1257_200805 ON
Caseload_Realignment.dbo.Teamcase.Case_No =
[1257data].dbo.CSE1257_200805.CaseId

WHERE
(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).

Thanks,
Laurie

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Share your knowledge

Related Discussions

Related Forums