Select one if there is duplicate in table-CodePudding

Select one if the image name is found in any column.

 name      |  col1      | col2
 -----------------------------
 Jack      | img_3.png  | img_1.png
 Jack      | img_2.png  | img_2.png
 Jack      | img_3.png  | img_1.png
 John      | img_4.png  | img_3.png

Now, What I want to get is I want to show user images, I don't need to fetch all the duplicated images, So its better to get one image only if there is duplicate found in any column.

Eg. For Jack I have to get only img_1.png, img_2.png, img_3.png file names, no need to get the images that found in any columns, just fetch the unique image names only.

What I have tried is

    SELECT col1, col2 FROM table_name WHERE name = '$name' GROUP BY
col1, col2

But it still gives me duplicated image names.

Note: There may be more columns But I only want to get unique images names that found in any of these all columns.

CodePudding user response：

Do try the following.

SELECT DISTINCT `col1` AS C1
FROM `table_name`
WHERE `name` = 'Jack'

UNION ALL

SELECT DISTINCT `col2` AS C2
FROM `table_name`
WHERE `name` = 'Jack'

GROUP BY 'C1'

CodePudding user response：

It looks like you can just us a simple union here - union by default will remove duplicates:

select name, col1
from t
union 
select name, col2
from t;

If you wanted to filter by user you can do that with an outer-select

select * from (
    select name, col1
    from t
    union 
    select name, col2
    from t
)t
where name = 'jack';