Assign null values to duplicate matching rows in a query

Question

I'm new to SQL

I have the below query,

select v.pattern_id,v.id,v.pdb_id as viocardId,p.pdb_id as PID,s.vio_id as SiVioId, r.date as orcdate, s.created_date as sivcreateddate
from orc_orcviolation v, orc_orcpattern p, siverify_siverifyverificationsite s, orc_orccheck o, orc_orcrecord r
where v.pattern_id = p.id
and p.pdb_id = s.pattern_id
and p.if_target = s.if_target
and p.if_secure <> 1
and v.check_id = o.id
and o.record_id = r.id

I get the following results for the above query,

pattern_id  id  viocardId   PID SiVioId orcdate sivcreateddate
469064  1205777 3024788 2335310 1739560 2014-05-02 16:14:32 2015-06-21 13:18:49
469064  1241653 3097963 2335310 1739560 2014-05-24 17:07:55 2015-06-21 13:18:49
469064  1309143 3393980 2335310 1739560 2014-07-19 17:49:11 2015-06-21 13:18:49
469064  1514825 4079963 2335310 1739560 2014-10-29 14:07:58 2015-06-21 13:18:49
469064  1516474 4085403 2335310 1739560 2014-10-30 20:52:10 2015-06-21 13:18:49
469064  1613816 4692514 2335310 1739560 2015-01-10 12:29:30 2015-06-21 13:18:49
469064  1631484 4728047 2335310 1739560 2015-01-23 19:49:30 2015-06-21 13:18:49
469064  1700479 5413265 2335310 1739560 2015-04-16 15:01:08 2015-06-21 13:18:49
469064  1702863 5416425 2335310 1739560 2015-04-22 00:07:01 2015-06-21 13:18:49
**469064    1739560 5540728 2335310 1739560 2015-06-17 05:12:05 2015-06-21 13:18:49**
469064  1911207 5818507 2335310 1739560 2015-12-04 07:53:33 2015-06-21 13:18:49
469064  1940132 5828037 2335310 1739560 2015-12-19 03:24:25 2015-06-21 13:18:49

In the ** marked row, the value of v.id and s.vio_id are matching, Is there any way apart from the matching row that the query returns null for other rows for sivioId column?

score 1 · Accepted Answer · edited May 23 '17 at 12:09

What you are looking for is the CASE statement.

You can choose what value to return depending on values in the result row. In this case you would return null when s.vio_id!=v.id.

SELECT v.pattern_id, v.id, v.pdb_id AS viocardId, p.pdb_id AS PID, 
    CASE WHEN s.vio_id=v.id THEN s.vio_id ELSE null END AS SiVioId,
    r.date AS orcdate, s.created_date AS sivcreateddate

FROM orc_orcviolation AS v
LEFT JOIN orc_orcpattern AS p ON v.pattern_id = p.id
LEFT JOIN siverify_siverifyverificationsite s
    ON p.pdb_id = s.pattern_id and p.if_target = s.if_target
LEFT JOIN orc_orccheck AS o ON v.check_id = o.id
LEFT JOIN orc_orcrecord AS r ON o.record_id = r.id
WHERE p.if_secure <> 1

The answer to your question comes from the SELECT clause of my query.
I rewrote your query using JOINs because I find it easier to see the connection between the tables. If you don't want to use the joins, feel free to only use the SELECT portion of the answer.

This query should result in:

pattern_id id      viocardId PID     SiVioId orcdate             sivcreateddate
469064     1205777 3024788   2335310 null    2014-05-02 16:14:32 2015-06-21 13:18:49
469064     1241653 3097963   2335310 null    2014-05-24 17:07:55 2015-06-21 13:18:49
469064     1309143 3393980   2335310 null    2014-07-19 17:49:11 2015-06-21 13:18:49
469064     1514825 4079963   2335310 null    2014-10-29 14:07:58 2015-06-21 13:18:49
469064     1516474 4085403   2335310 null    2014-10-30 20:52:10 2015-06-21 13:18:49
469064     1613816 4692514   2335310 null    2015-01-10 12:29:30 2015-06-21 13:18:49
469064     1631484 4728047   2335310 null    2015-01-23 19:49:30 2015-06-21 13:18:49
469064     1700479 5413265   2335310 null    2015-04-16 15:01:08 2015-06-21 13:18:49
469064     1702863 5416425   2335310 null    2015-04-22 00:07:01 2015-06-21 13:18:49
469064     1739560 5540728   2335310 1739560 2015-06-17 05:12:05 2015-06-21 13:18:49
469064     1911207 5818507   2335310 null    2015-12-04 07:53:33 2015-06-21 13:18:49
469064     1940132 5828037   2335310 null    2015-12-19 03:24:25 2015-06-21 13:18:49

Assign null values to duplicate matching rows in a query

1 Answers1