I've got a table with 2 posts, both getting names
I would like a question that finds only unique names - names that only appear once max within the entire table.
For instance, for an additional table:
NAME1 | NAME2 -------------------- DAN MIKE MIKE TONY FOO DAN
It will only return FOO and TONY.
I suppose you could do this it a couple of ways, but here's one (presuming Name1 and Name2 are of the identical, or unconditionally convertible types):
SELECT [Name] FROM (SELECT Name1 AS [Name] FROM [Table] UNION ALL SELECT Name2 FROM [Table]) GROUP BY [Name] HAVING COUNT(*) = 1
What about the easy:
select name1 from table where name1 not in (select distinct name2 from table ) union select name2 from table where name2 not in (select distinct name1 from table );
As lengthy as
name2 can't be null, make use of this query:
SELECT t.Name1 FROM tableName t LEFT JOIN tableName t2 ON t.NAME1 = t2.NAME2 WHERE t2.NAME2 IS NULL GROUP BY t.Name1 HAVING COUNT(*) = 1 UNION ALL SELECT t.Name2 FROM tableName t LEFT JOIN tableName t2 ON t.NAME2 = t2.NAME1 WHERE t2.NAME1 IS NULL GROUP BY t.Name2 HAVING COUNT(*) = 1
Whether they can be null, make use of this query:
SELECT Name FROM ( SELECT t.Name1 Name FROM tableName GROUP BY t.Name1 HAVING COUNT(*) = 1 UNION ALL SELECT t.Name2 Name FROM tableName GROUP BY t.Name2 HAVING COUNT(*) = 1 ) rs GROUP BY Name HAVING COUNT(*) = 1
@CheeseConQueso construed the question in a different way than Used to do. I figured you meant unique names from both posts combined.
Here's my version:
select name, count(*) from (select name1 as name from table union select name2 as name from table) group by name having count(*) = 1;
You are able to wrap the entire factor inside a "choose title from ..." to obtain the names alone, or this may be rewritten utilizing an analytic function rather than grouping to filter the replicates. Is dependent around the abilities of the database.