CODEWITHSUNDEEP
sqlpostgresql
OozeMeister
OozeMeister

Reputation: 4869

SQL - Set certain columns to null based on condition

Say I have a table named people with various attributes

I would like to make a query that will return all people, however, under a certain condition (a separate subquery) I would like for certain columns of the select statement to be set to null for redacting purposes.

So, say I have the basic query:

SELECT first_name, last_name, age, phone, address
FROM people;

Which would return a table similar to:

|first_name|last_name|age|phone|address       |
------------------------------------------------
|Joe       |Someone  |12 |123  |1234 Somewhere|
|Bob       |Other    |45 |982  |4321 Somewhere|

I would like to be able to do something like this:

SELECT
    first_name,
    last_name,
    if(people.field IN (
        SELECT other_table.some_id FROM other_table WHERE other_table.field = 'is good')
    )
        age, phone, address
    else
        NULL
FROM people;

With the result I would like to have:

|first_name|last_name|age  |phone|address       |
------------------------------------------------
|Joe       |Someone  |12   |123  |1234 Somewhere|
|Bob       |Other    |NULL |NULL |NULL          |

Is this even possible?

Upvotes: 2

Views: 1837

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You can accomplish this with a lateral join:

select p.first_name, p.last_name, s.*
from people p left join lateral
     (select p.age, p.phone, p.address
      from othertable ot
      where ot.other_id = p.field
     ) s;

The subquery will return no rows if there is not a match -- hence all the columns will be NULL. Otherwise, it will return the fields from people.

Upvotes: 1

FXD
FXD

Reputation: 2060

I would recommend using a solution based on EXISTS which will be able to support any kind on multiplicity in other_table.
Note: you usually want to "link" values from people with values from other_table by referencing the correct fields in the sub-query, which I have done for first_name and last_name.

SELECT
    first_name,
    last_name,
    CASE WHEN MyCondition THEN NULL ELSE age     END AS Age,
    CASE WHEN MyCondition THEN NULL ELSE phone   END AS Phone,
    CASE WHEN MyCondition THEN NULL ELSE address END AS Addess
FROM (
SELECT people.*, EXISTS (SELECT 1 FROM other_table WHERE field = 'is good' AND first_name = P.first_name and last_name=P.last_name) as MyCondition
FROM people P
) MyTable

PS: in case you want to extend this to other DBMS, remember that some may not support EXISTS(...) at the place where I typed it (example of such DBMS: Oracle).
In such a case, you need to do a CASE WHEN EXISTS(...) THEN 1 ELSE 0 END instead.

Upvotes: 2

GMB
GMB

Reputation: 222582

It should be possible to solve this by puting the subquery in a LEFT JOIN and then using CASE in the SELECT clause to set the output columns to NULL when needed :

SELECT
    p.first_name,
    p.last_name,
    CASE WHEN s.some_id IS NOT NULL THEN p.age     END AS age,
    CASE WHEN s.some_id IS NOT NULL THEN p.phone   END AS phone,
    CASE WHEN s.some_id IS NOT NULL THEN p.address END AS address
FROM people p
LEFT JOIN other_table s ON s.some_id = p.field AND s.field = 'is good'

The thing that you need to be careful about is the cardinality of the join. If several records in other_table match a single people record, then you will get duplicated output rows. To avoid that, one solution would be to use SELECT DISTINCT, or to turn on aggregation by adding clause GROUP BY p.first_name, p.last_name, p.age, p.phone, p.address at the end of the query.

Upvotes: 2

Related Questions