CODEWITHSUNDEEP
sqlsql-server
Bakeduck2021
Bakeduck2021

Reputation: 31

Combine two tables in SQL with only unique rows based on a column

I have two tables LeftCamera and RightCamera with the same schema. The column headers are FrameNumber, X, Y, and TestId. I would like to be able to combine the two tables with some stipulations. 1. I want to combine the two tables where FrameNumber is unique. 2. Include X, Y, and TestId for each unique FrameNumber. 3. Choose the LeftCamera over the RightCamera when a duplicate FrameNumber appears. And 4. only combine the rows together where TestId of LeftCamera is equal to TestId of RightCamera.

This query returns all the unique FrameNumber values, which is what I want, but it doesn't include the rest of the table and it's not specific to TestNumber.

Select FrameNumber From LeftCamera
UNION
Select FrameNumber From RightCamera
Order By FrameNumber

This is closer but it still gives me duplicate FrameNumber and doesn't account for TestId

Select FrameNumber, X, Y, TestId
FROM LeftCamera
UNION
Select FrameNumber, X, Y, TestId
FROM RightCamera AS RC
Where Not Exists (Select 1 From LeftCamera AS LC
                  Where RC.FrameNumber = LC.FrameNumber)
Order By FrameNumber

This is my desired results enter image description here . I'm a little stuck and could use some help. I'm using SQL server. Thanks in advance.

Upvotes: 0

Views: 656

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

If you want to solve this with UNION, you are quite close. Your issue is that in the NOT EXISTS clause you are looking at the FrameNumber only, while you are really interested in FrameNumber in combination with TestId.

The same applies for the ORDER BY: Don't look at the FrameNumber alone, order by TestId and FrameNumber instead.

At last, you should use UNION ALL because the NOT EXISTS already prevents from getting duplicates.

Select FrameNumber, X, Y, TestId
FROM LeftCamera
UNION ALL
Select FrameNumber, X, Y, TestId
FROM RightCamera AS RC
Where Not Exists (Select 1 From LeftCamera AS LC
                  Where RC.TestId = LC.TestId
                  And RC.FrameNumber = LC.FrameNumber)
Order By TestId, FrameNumber;

I have added this answer in order to build up on your query and point out your mistakes. You see, however, that this is less readable than a simple full outer join. But if your DBMS happens to lack full joins, this is a viable solution.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You seem to want a FULL OUTER JOIN and COALESCE:

select
  testid, framenumber,
  coalesce(l.x, r.x) as x,
  coalesce(l.y, r.y) as y  
from leftcamera l full outer join rightcamera r using (testid, framenumber)
order by testid, framenumber;

This is standard SQL. Your DBMS may or may not support full outer joins, coalesce and using clauses. If it lacks any of this, look up how to emulate the missing feature in your DBMS.

Upvotes: 1

Related Questions