SQL - how to find unique values from 2 columns?-CodePudding

I'm using SQLite, and would like to get only rows with values that are unique per table. I know how to get values per each column, but can not create a single (sqlite compatible) select to get unique value from both column.

Here is data example:

col_A	col_B
111	777
222	333
222	111
444	333
222	555
333	666
333	777

My desired result from above example would be rows with unique values that include only: 444, 555, 666.

col_A	col_B
444	333
222	555
333	666

222 should not be in results as it appears multiple times in col_A. 777 should not be in results as it appears multiple times in col_B. I need only single occurrence of value in either col_A or col_B. Unique value per table, not column.

How far i managed to get:

SELECT * 
    FROM my_table
    WHERE 
        (SELECT col_A as asset from my_table GROUP BY col_A HAVING COUNT(*) = 1
         UNION
        SELECT col_B as asset from my_table GROUP BY col_B HAVING COUNT(*) = 1)
            IN (col_A, col_B)

I have tried with UNION and few other approaches, but can not achieve desired result.

CodePudding user response：

To Start filtering by UNION ALL considering the keyword either would be suitable option along with HAVING clause to determine the distinct values such as

WITH t1 AS  
(
 SELECT col
   FROM (SELECT col_A AS col  FROM t UNION ALL
         SELECT col_B         FROM t) AS tt
  GROUP BY col
 HAVING COUNT(*)=1 
)
SELECT t.* 
  FROM t1
  JOIN t
    ON col IN ( col_A , col_B )

Demo

CodePudding user response：

I think you need a query like this:

;with unqs as (
  select col_c
  from (
    select col_c, count(col_c) cnt
    from (
      select col_A as col_c
      from my_table
      union all
      select col_B as col_c
      from my_table) as t
     group by col_c) as tt
  where cnt = 1
)
select *
from my_table
where col_A in (select col_c from unqs)
   or col_B in (select col_c from unqs);

SQL Fiddle