sqlduplicatesunionsql-except

SQL, combine two tables with distinct values only (based on only three fields, not entire row)

I'm trying to combine three tables, Application, Audio, Video. They all have the same schema, but there are 'duplicates' in Audio and Video. Where an entry in Video will always also show in Audio. I put duplicates in quotes as the entire row isn't duplicated, but based off three fields they are.

Audio Table

   **Calls   | StartTime    | EndTime       | Quality | CallType**
      John   | 3/15/16 8:01 | 3/15/16 9:01  |   0     |   Audio
      Mary   | 3/15/16 9:35 | 3/15/16 10:34 |   1     |   Audio

Video Table

   **Calls   | StartTime     | EndTime       | Quality | CallType**
      John   | 3/15/16 8:01  | 3/15/16 9:01  |   1     |   Video
      Sue    | 3/15/16 11:32 | 3/15/16 11:50 |   0     |   Video

Application Table

   **Calls   | StartTime     | EndTime       | Quality | CallType**
      Matt   | 3/15/16 7:31  | 3/15/16 8:01  |   1     |   App
      Bill   | 3/15/16 1:32  | 3/15/16 2:50  |   0     |   App

and I want to combine them into one table that keeps the data from Video when there are duplicates- so you see John only shows up once, with the quality and calltype from Video table:

   **Calls   | StartTime     | EndTime       | Quality | CallType**
      Mary   | 3/15/16 9:35  | 3/15/16 10:34 |   1     |   Audio
      John   | 3/15/16 8:01  | 3/15/16 8:01  |   1     |   Video
      Sue    | 3/15/16 11:32 | 3/15/16 11:50 |   0     |   Video
      Matt   | 3/15/16 7:31  | 3/15/16 8:01  |   1     |   App
      Bill   | 3/15/16 1:32  | 3/15/16 2:50  |   0     |   App

I was trying to use EXCEPT and UNION, to choose all the rows in audio that don't show up in video, then joining with video, but since it's seeing them as unique because calltype is always different, and quality could potentially be different.

   SELECT Calls, StartTime, EndTime, Quality, CallType
   FROM Audio

   EXCEPT
   SELECT Calls, StartTime, EndTime, Quality, CallType
   FROM Video

   UNION
   SELECT Calls, StartTime, EndTime, Quality, CallType
   FROM Video

   UNION
   SELECT Calls, StartTime, EndTime, Quality, CallType
   FROM Application
Solution

  • Hmmm, I would do this as:

    SELECT Calls, StartTime, EndTime, Quality, CallType
FROM Application
UNION ALL
SELECT Calls, StartTime, EndTime, Quality, CallType
FROM Video
UNION ALL
SELECT Calls, StartTime, EndTime, Quality, CallType
FROM Audio a
WHERE NOT EXISTS (SELECT 1
                  FROM Video v
                  WHERE v.Calls = a.Calls and v.StartTime = a.StartTime and v.EndTime = a.EndTime
                 );

    Notes: