CODEWITHSUNDEEP
sqlite
RockGuitarist1
RockGuitarist1

Reputation: 716

SQLite Join Causing Duplicate Data After First Correct Record

I am having an issue where after the first record in a Select statement the data in a certain column is being duplicated within the Select statement. I will provide the statement in which I am working with:

SELECT Call.CallID AS [Call #],
   (Members.FirstName || ' ' || Members.LastName) AS OIC,
   Alarm.AlarmID,
   Alarm.AlarmDesc AS Alarm,
 --Group_Concat(m2.FirstName || ' ' || m2.LastName) AS [Line Officers],
   Group_Concat(m1.FirstName || ' ' || m1.LastName) AS Members
FROM Call
   LEFT JOIN
   Members ON Call.OIC = Members.MemberID
   LEFT JOIN
   Alarm ON Call.AlarmID = Alarm.AlarmID
   LEFT JOIN
   CallToMembers ON Call.CallID = CallToMembers.CallID
 --LEFT JOIN 
 --CallToLineOfficers on Call.CallID = CallToLineOfficers.CallID
   LEFT JOIN
   Members AS m1 ON CallToMembers.MemberID = m1.MemberID
 --LEFT JOIN
 --Members as m2 on CallToLineOfficers.MemberID = m2.MemberID
GROUP BY Call.CallID;

Ok, so this statement returns everything that I need and is working perfectly. However, whenever I uncomment the lines that are commented out so that I can obtain "Line Officers" from the Bridge Table "CallToLineOfficers", data begins to duplicate from within the columns like so:

Incorrect Complete Select Statement - Nothing commented out. Incorrect Complete Select Statement Correct Line Officers - If I comment Joins for CallToMembers. Correct Line Officers Correct Members - If I comment Joins for CallToLineOfficers. Correct Members

As you can see that once I introduce both "Members" and "Line Officers", things go wrong.

Upvotes: 1

Views: 36

Answers (1)

CL.
CL.

Reputation: 180101

When you join the calls with the line officers, you get an intermediate result like this:

Call #  Line Officers
------  -------------
54      Bob Clark
54      Rob Catalano

When you then join with the members, the database matches the call number again, so for each row in the intermediate result, you get all combinations with the members:

Call #  Line Officers  Members
------  -------------  -----------
54      Bob Clark      Matt Butler
54      Rob Catalano   Matt Butler
54      Bob Clark      Tom Cramer
54      Rob Catalano   Tom Cramer
...

So you cannot use joins when you have multiple independent tables with 1:N relationships.

You don't actually want to have multiple line officer/member rows in the result anyway, so you can just use a subquery to aggregate those values:

SELECT CallID,
       (SELECT group_concat(FirstName || ' ' || LastName)
        FROM CallToLineOfficers
        JOIN Members USING (MemberID)
        WHERE CallID = Call.CallID
       ) AS "Line Officers",
       (SELECT group_concat(FirstName || ' ' || LastName)
        FROM CallToMembers
        JOIN Members USING (MemberID)
        WHERE CallID = Call.CallID
       ) AS Members
FROM Call;

Upvotes: 1

Related Questions