Select duplicate records with error message while querying on UNION ALL

Question

SELECT _id, col1, col2, jobid, error_msg FROM table_name1
WHERE tn1.jobid in (1,2,3)
UNION ALL
SELECT _id, col1, col2, jobid, error_msg FROM table_name2
WHERE tn1.jobid in (1,2,3)
UNION ALL
SELECT _id, col1, col2, jobid, error_msg FROM table_name3
WHERE tn1.jobid in (1,2,3)

For the above query the output will look below

_id, col1, col2, jobid, error_msg
1, "Hi", "Hel", 1, "Data Invalid"
1, "Hi1", "Hel", 2, ""
2, "Hello", "DupTest",3, "" 
3, "Nota Dup", "All is Good",3 ,""

I need to raise error message if there is duplicates , for the above case _id (1) need to select error message as "Duplicate Entries Present"

My solution looks below

WITH tmp AS
(
  SELECT _id , count(1) as cnt FROM
  (
    SELECT _id FROM table_name1
    WHERE tn1.jobid in (1,2,3)
    UNION ALL
    SELECT _id FROM table_name2
    WHERE tn1.jobid in (1,2,3)
    UNION ALL
    SELECT _id FROM table_name3
    WHERE tn1.jobid in (1,2,3) 
   )
   GROUP BY _id
)
SELECT _id, col1, col2, jobid, 
CASE WHEN tmp.cnt > 1 THEN "Dup present" ELSE NULL as error_msg
FROM table_name1
JOIN tmp USING(_id)
WHERE tn1.jobid in (1,2,3)
    UNION ALL
    SELECT _id, col1, col2, jobid
CASE WHEN tmp.cnt > 1 THEN "Dup present" ELSE NULL as error_msg
FROM table_name2
JOIN tmp USING(_id)
    WHERE tn2.jobid in (1,2,3)
    UNION ALL
    SELECT _id, col1, col2, jobid
CASE WHEN tmp.cnt > 1 THEN "Dup present" ELSE NULL as error_msg
FROM table_name3
JOIN tmp USING(_id)
    WHERE tn3.jobid in (1,2,3)

Could you suggest is there better solution than this?

Possible one more option create it as View to use it on multiple places

CREATE TEMP VIEW tmp AS 
SELECT _id , count(1) as cnt FROM
      (
        SELECT _id FROM table_name1
        WHERE tn1.jobid in (1,2,3)
        UNION ALL
        SELECT _id FROM table_name2
        WHERE tn1.jobid in (1,2,3)
        UNION ALL
        SELECT _id FROM table_name3
        WHERE tn1.jobid in (1,2,3) 
       )
       GROUP BY _id

What I have done bad ? why negative for my question ? – Shankar Nov 01 '15 at 06:17 — Shankar, Nov 01 '15 at 06:17

score 0 · Accepted Answer · edited Nov 01 '15 at 06:52

0

You can use window function:

with cte as(SELECT _id, col1, col2, jobid, error_msg FROM table_name1
            WHERE tn1.jobid in (1,2,3)
            UNION ALL
            SELECT _id, col1, col2,jobid, error_msg FROM table_name2
            WHERE tn1.jobid in (1,2,3)
            UNION ALL
            SELECT _id, col1, col2,jobid, error_msg FROM table_name3
            WHERE tn1.jobid in (1,2,3))

select *, CASE WHEN count(*) over(partition by _id) > 1 
               THEN CONCAT_WS(',' , cte.error_msg, "Dup present")
               ELSE cte.error_msg
from cte

edited Nov 01 '15 at 06:52

Shankar

846
8
24

answered Nov 01 '15 at 06:07

Giorgi Nakeuri

35,155
8
47
75

Thanks for your solution, I think what if the case "cte" table has some other error message, we need to concat those error messages too. But I didn't mention about it in the question though – Shankar Nov 01 '15 at 06:17

score 0 · Answer 2 · edited May 23 '17 at 12:23

Window function solution , Credit goes to Find duplicate rows with PostgreSQL

WITH tmp AS
(
  SELECT _id,
  ROW_NUMBER() OVER(PARTITION BY _id) AS cnt
  FROM
  (
    SELECT _id FROM table_name1
    WHERE tn1.jobid in (1,2,3)
    UNION ALL
    SELECT _id FROM table_name2
    WHERE tn1.jobid in (1,2,3)
    UNION ALL
    SELECT _id FROM table_name3
    WHERE tn1.jobid in (1,2,3) 
   )

)
SELECT _id, col1, col2, jobid, 
CASE WHEN tmp.cnt > 1 THEN "Dup present" ELSE NULL as error_msg
FROM table_name1
JOIN tmp USING(_id)
WHERE tn1.jobid in (1,2,3)
    UNION ALL
    SELECT _id, col1, col2, jobid
CASE WHEN tmp.cnt > 1 THEN "Dup present" ELSE NULL as error_msg
FROM table_name2
JOIN tmp USING(_id)
    WHERE tn2.jobid in (1,2,3)
    UNION ALL
    SELECT _id, col1, col2, jobid
CASE WHEN tmp.cnt > 1 THEN "Dup present" ELSE NULL as error_msg
FROM table_name3
JOIN tmp USING(_id)
    WHERE tn3.jobid in (1,2,3)

Select duplicate records with error message while querying on UNION ALL

2 Answers2