CODEWITHSUNDEEP
sqloracle-database
muhnizar
muhnizar

Reputation: 327

Flag row has duplicate value

I have query problem. I have 3 table.

table A 
    ----------------------------
    NAME   | CODE 
    ----------------------------
    bob    | PL
    david  | AA
    susan  | PL
    joe    | AB
    alfred | PL



table B 
    ----------------------------
    CODE | DESCRIPTION
    ----------------------------
    PL   | code 1
    PB   | code 2 
    PC   | code 3

table C 
    ----------------------------
    CODE | DESCRIPTION
    ----------------------------
    AA   | code 4
    AB   | code 5 
    AC   | code 6

Table B and C have unique row. the result I need to flag which row has duplicate records:

    -------------------------------------
    NAME   | CODE | DESCRIPTION | DUPLICATE
    -------------------------------------
    bob    | PL   | code 1      | YES
    david  | AA   | code 4      | NO
    susan  | PL   | code 1      | YES
    joe    | AB   | code 5      | NO
    Alfred | PL   | code 1      | YES

What I have tried so far

http://sqlfiddle.com/#!9/ffb2eb/16

Upvotes: 0

Views: 196

Answers (2)

psaraj12
psaraj12

Reputation: 5072

Try the below code in MYSQL (http://sqlfiddle.com/#!9/9a149b/1)

Note :- The code works in Oracle as well http://sqlfiddle.com/#!4/6c6df/5 

select A.*, 
COALESCE(B.DESCRIPTION, C.DESCRIPTION) AS DESCRIPTION,
 dup.DUPLICATE
 from A 
left join B on A.CODE = B.CODE
left join C on A.CODE = C.CODE
INNER JOIN (select a.code,
        case when count(a.code) > 1 then 'YES' else 'NO' end as 
        DUPLICATE 
        from A 
        group by a.code) dup
        ON A.code=dup.code

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I don't see why you need tables B and C at all to calculate the flag. You can do this with window functions:

select a.*,
       (case when count(*) over (partition by a.code) > 1 then 'YES' else 'NO'
        end) as flag
from a;

You do need B and C if you want to bring in the additional values:

select a.*, coalesce(b.description, c.description) as description,
       (case when count(*) over (partition by a.code) > 1 then 'YES' else 'NO'
        end) as flag
from a left join
     b
     on a.code = b.code left join
     c
     on a.code = c.code;

Here is a working Oracle SQL Fiddle.

Upvotes: 2

Related Questions