How to Do a Sub Query and JOIN for Comparison Report

Question

Hi I was trying to do a Sub Query and Comparison Report

For Example, I have a Table of Teams with Color AND Name in a Single Table. A person can be on more than one Team and Multiple Team Colors can exist in the table.

CREATE TABLE [TEAMS](
    [Color] [nvarchar](10) NULL,
    [Name] [nvarchar](10) NULL
);

INSERT INTO TEAMS (Color,Name) VALUES
     (N'Red',N'Austin'),
     (N'Blue',N'Austin'),
     (N'Red',N'James'),
     (N'Yellow',N'Melissa'),
     (N'Blue',N'Brandon');

I was trying to get a query to help identify the differences between two Teams like who is different between the Red and Blue Team and get something like this.

I was hoping to do a Full Join on the Name with NULLs in Table A and NULLs in Table B to Find the Differences but because its the same Table No NULLS are Occurring.

The Query I was doing for my comparison is this.

Select a.Color, a.Name, b.Color, b.Name from TEAMS a
FULL JOIN TEAMS b on a.Name = b.Name
WHERE a.Color = 'Red' AND b.Color = 'Blue';

Removing the WHERE CLAUSE is also Revealing that Team Color is Comparing to itself (Red Team A to Red Team B, etc...) and the team I'm not interested in (Yellow) is also being used in the Query and eating Resources.

This seems possible with Temporary Tables if just Red Team was in Table A and just Blue Team was in Table B but is there a Way to Do this with some form of Sub Query?

Answer 1

This would not achieve the same layout as you specified, but it would list the names that are on one team that are lacking from another specific team. It is also highly inefficient for large-scale operations. It also would not directly scale to the three-way comparison with yellow especially if you want additional categories (yellow-only, red-only, blue-only, not-yellow, not-red, not-blue, all teams). But for a small dataset and comparing any 2 teams at a time, it should work.

SELECT a.Color+"-Only", a.Name
FROM TEAMS a
WHERE a.Color in ('Red','Blue') 
  and a.name not in (SELECT
     b.name from TEAMS b WHERE b.Color in ('Red','Blue') and b.Color <> a.Color
  ) s

some RDBMSes may require the subselect to be aliased (s here) while others may not.

You can read the query as: for a given player name on one of two teams, is that name NOT on the other team? if so, give me their name and which team they ARE on.