SELECT
p.patstoreid, p.patkey, p.stseq,
a.keystoreid, a.keykey, a.seq,
(p.patstoreid = a.keystoreid) as match,
(p.stseq != a.seq) as mismatch
FROM patient p, address a
WHERE p.patstoreid = a.keystoreid AND p.patkey = a.keykey AND p.stseq = a.seq
The results ignore the "p.stseq = a.seq" clause. But by listing the values for p.stseq, a.seq, match and mismatch I can see that my code is providing the correct values and Postgres makes the expected comparisons.
Sample correct results:
102146 | 42 | 1 | 102146 | 42 | 1 | t | f
102146 | 171 | 1 | 102146 | 171 | 1 | t | f
102146 | 256 | 1 | 102146 | 256 | 1 | t | f
102146 | 288 | 1 | 102146 | 288 | 1 | t | f
Sample incorrect results:
102146 | 1818 | 2 | 102146 | 1818 | 1 | f | t
102146 | 2049 | 2 | 102146 | 2049 | 1 | f | t
102146 | 3823 | 2 | 102146 | 3823 | 1 | f | t
102146 | 4271 | 4 | 102146 | 4271 | 1 | f | t
It works if I remove the "p.patstoreid = a.keystoreid" clause for this particular data where those values are always the same. But I need that clause for other datasets where the values don't always match.
SELECT p.patstoreid, p.patkey, p.stseq, a.keystoreid, a.keykey, a.seq, (p.patstoreid = a.keystoreid) as match, (p.stseq != a.seq) as mismatch FROM patient p, address a WHERE p.patstoreid = a.keystoreid AND p.patkey = a.keykey AND p.stseq = a.seq
The results ignore the "p.stseq = a.seq" clause. But by listing the values for p.stseq, a.seq, match and mismatch I can see that my code is providing the correct values and Postgres makes the expected comparisons.
Sample correct results: 102146 | 42 | 1 | 102146 | 42 | 1 | t | f 102146 | 171 | 1 | 102146 | 171 | 1 | t | f 102146 | 256 | 1 | 102146 | 256 | 1 | t | f 102146 | 288 | 1 | 102146 | 288 | 1 | t | f
Sample incorrect results: 102146 | 1818 | 2 | 102146 | 1818 | 1 | f | t 102146 | 2049 | 2 | 102146 | 2049 | 1 | f | t 102146 | 3823 | 2 | 102146 | 3823 | 1 | f | t 102146 | 4271 | 4 | 102146 | 4271 | 1 | f | t
It works if I remove the "p.patstoreid = a.keystoreid" clause for this particular data where those values are always the same. But I need that clause for other datasets where the values don't always match.