Filter value from many columns in table-CodePudding

I trying to create SQL question to my PostgreSQL database. I have table looks like as:

id | column1 | column2| column3 | column4
1  |   1     |   2    |    3    |   1
2  |   21    |   22   |    23   |   5
3  |   31    |   22   |    32   |   6
4  |   54    |   43   |    45   |   4
5  |   43    |   23   |    34   |   4
6  |   43    |   54   |    43   |   2
7  |   54    |   52   |    53   |   8
8  |   21    |   2211 |    43   |   4
9  |   43    |   33   |    45   |   9
10 |   87    |   62   |    11   |   3

I try to write question for return only unique value for chosen column, for example column1:

id | column1 | column2| column3 | column4
1  |  1      |   2    |    3    |   1
2  |  21     |   22   |    23   |   5
3  |  31     |   22   |    32   |   6
8  |  21     |   2211 |    43   |   4
10 |  87     |   62   |    11   |   3

My request:

SELECT id, column1, column2, column3, column4
FROM test AS T
WHERE T.column1 != T.column2
   OR T.column1 != T.column3

In response, I get the entire table, not unique records.

Can someone tell me why this is not working and explain how to write a query that will work.

CodePudding user response：

If I understand correctly, you want rows where their value in column1 does not appear in any other row's column2, column3, or column4.

For this you can do a subquery to check each row against every other row.

select *
from test t1
where not exists (
  select id
  from test t2
  where t2.id <> t1.id and (
     t2.column2 = t1.column1 or
     t2.column3 = t1.column1 or
     t2.column4 = t1.column1
  )
)

Demonstration

Note that the need to check different columns for uniqueness suggests that column1, column2, column3, and column4 are actually a list of the same type of value. It would be better to store the values a join table.

create table test (
  id bigserial primary key
);

create table test_stuff (
  test_id bigint not null references test,
  value integer not null
);

insert into test values (1);
insert into test_stuff values (1, 1), (1,2), (1,3), (1,1);

CodePudding user response：

It would be something like this:

SELECT *
FROM TEST
WHERE
    COLUMN1 NOT IN (
        SELECT COLUMN2
        FROM TEST
    )
    AND COLUMN1 NOT IN (
        SELECT COLUMN3
        FROM TEST
    )

You can try here: https://www.db-fiddle.com/f/mmMr3vz2JHvfBabu62Si79/0

CodePudding user response：

The description does not match the expected result. That being said, the following query should produce the expected result. It finds all rows for which there are no duplicates in column 1:

select *
from t
where not exists (
    select *
    from t as x
    where x.column1 = t.column1 and x.id <> t.id
)