CODEWITHSUNDEEP
sqlms-access
user820304
user820304

Reputation:

JOIN two tables, but only include data from first table in first instance of each unique record

Title might be confusing.

I have a table of Cases, and each Case can contain many Tasks. To achieve a different workflow for each Task, I have different tables such as Case_Emails, Case_Calls, Case_Chats, etc...

I want to build a Query that will eventually be exported to Excel. In this query, I want to list out each Task, and the Tasks are already joined together via a UNION in another table using a common format. For each task in the Query, I want only the first Task associated with a case to include the details from Cases table. Example below:

+----+---------+------------+-------------+-------------+-------------+
| id | Case ID | Agent Name | Task Info 1 | Task Info 2 | Task Info 3 |
+----+---------+------------+-------------+-------------+-------------+
| 1  | 4000000 | Some Name  | Detailstuff | Stuffdetail | Thingsyo    |
| 2  |         |            | Detailstuff | Stuffdetail | Thingsyo    |
| 3  |         |            | Detailstuff | Stuffdetail | Thingsyo    |
| 4  | 4000003 | Some Name  | Detailstuff | Stuffdetail | Thingsyo    |
| 5  |         |            | Detailstuff | Stuffdetail | Thingsyo    |
| 6  | 4000006 | Some Name  | Detailstuff | Stuffdetail | Thingsyo    |
+----+---------+------------+-------------+-------------+-------------+

My original approach was attempting a LEFT JOIN on Case ID, but I couldn't figure out how to filter the data out from the extra rows.

Upvotes: 0

Views: 59

Answers (3)

Jerrad
Jerrad

Reputation: 5290

This would be much simpler if Access supported the ROW_NUMBER function. It doesn't, but you can sort of simulate it with a correlated subquery using the Tasks table (this assumes that each task has a unique numeric ID). This basically assigns a row number to each task, partitioned by the CaseID. Then you can just conditionally display the CaseID and AgentName where RowNum = 1.

SELECT Switch(RowNum = 1, CaseID) as Case, 
Switch(RowNum = 1, AgentName) as Agent, 
TaskName
FROM (
    SELECT c.CaseID, 
    c.AgentName, 
    t.TaskName, 
    (select count(*) 
     from Tasks t2 
     where t2.CaseID = c.CaseID and t2.ID <= t.ID) as RowNum
    FROM Cases c
    INNER JOIN Tasks t ON c.CaseID = t.CaseID
    order by c.CaseID, t.TaskName
)

You didn't post your table structure, so I'm not sure this will work for you as-is, but maybe you can adapt it.

Upvotes: 1

user820304
user820304

Reputation:

Found out that I can name my tables in the query like so:

FROM Case_Calls Calls

With this other name, I was able to filter based on a sub query:

IIF( Calls.[ID] <> (select top 1 [ID] from Case_Calls where [Case ID] = Calls.[Case ID]), '', Cases.[Creator]) As [Case Creator]

This solution gives me the results that I want :) It's rather ugly SQL, and difficult to parse when I'm dealing with dozens of columns, but it gets the job done!

I'm still curious if there is a better solution...

Upvotes: 0

Tdubs
Tdubs

Reputation: 29

No matter what when you join you will have duplicate values. to remove the duplicates either put in a Distinct in your select or a Group by after your filters. This should resolve the duplicates in you query for task info 1,2,3.

Upvotes: 0

Related Questions