Pedram Salamati Pedram Salamati - 6 months ago 24
MySQL Question

How to Identify a new value

I have 3 tables joined to each other to get information of my clients. some of my clients are multiracial and that makes the table to have duplicate rows.
I was wondering if I can identify a new value and say if an ID duplicate, in race gave me Multiracial as a value?

I have this:

ID Name Age Race
4000 Duc 25 Philipino
4000 Duc 25 Chinese
4001 Daniel 36 African American
4002 Nicole 20 White
4002 Nicole 20 Middle East


I want to have this

ID Name Age Race
4000 Duc 25 Multiracial
4001 Daniel 36 African American
4002 Nicole 20 Multiracial


I really appreciate any help

Answer

You can do that (should work on MS SQL and MySQL):

SELECT ID,Name,Age, CASE WHEN COUNT(Race)>1 THEN 'Mulitiracial' ELSE MAX(Race) END Race
FROM Src
GROUP BY ID,Name,Age

To test your data (CTE only in MS SQL) I've used following:

WITH Src AS
(
    SELECT * FROM (VALUES
    (4000, 'Duc   ', 25, 'Philipino'        ),
    (4000, 'Duc   ', 25, 'Chinese'          ),
    (4001, 'Daniel', 36, 'African American' ),
    (4002, 'Nicole', 20, 'White           ' ),
    (4002, 'Nicole', 20, 'Middle East     ' )
    )T (ID,Name,Age,Race)
)
SELECT ID,Name,Age, CASE WHEN COUNT(Race)>1 THEN 'Mulitiracial' ELSE MAX(Race) END Race
FROM Src
GROUP BY ID,Name,Age