How to remove duplicate entries? SQL Server 2008

Question

Apologies if this question has already been asked, but i'm pulling my hair out!

I have two tables, abbreviated to KI and UG. KI contains a list of people and their photos, UG contains another list of people. What I want to do is match the tables up and return a query that shows me a list of names where we have a match between KI & UG. Now i'm halfway there, i've got my query and it works fine (almost) - the problem is that there are loads of duplicates in the list. The people that originally managed the KI table had input different images for the same person, leaving there to be multiple rows for "John Smith" for example.

This is my code:

SELECT ki.name, ug.name, ki.image_file
FROM kantechimages AS ki
INNER JOIN user_group as UG ON ki.name like ug.name
WHERE ki.image_file is not null
GROUP BY ki.name, ug.name, ki.image_file

So my question is, how can I remove the duplicate names from the list and only return one row where we have a match instead of all of them?

Many thanks!

How do you decide which row should be returned among the duplicates? — shree.pat18, Jan 22 '15 at 11:11
If you want one row per person which image_file do you want for that person? What is the data type of the image_file column, is it a filename or actual image data? — Rhys Jones, Jan 22 '15 at 11:12
I just wanted to return the person's name from both tables to double check it was an exact match, and i needed to return the image_file as i need it to identify where the image is. The image_file column is an 'nvarchar' and just contains a file name of the image on the server. With regards to the image file i want, it doesn't really matter as long as there is one. The people that managed the database before were careless and didn't remove the old ones when they added new ones in. — Sam, Jan 22 '15 at 11:12
If there are multiple image_file per user then how will you decide which you need (if you are going to select first one then just remove image_file from your above query, make it distinct as well and then retrieve top 1 image from many) — Nilesh Thakkar, Jan 22 '15 at 11:14

score 0 · Accepted Answer · answered Jan 22 '15 at 11:17

This will give you one row per person, but only one image file too;

SELECT ki.name, ug.name, max(ki.image_file) as image_file
FROM kantechimages AS ki
INNER JOIN user_group as UG ON ki.name like ug.name
WHERE ki.image_file is not null
GROUP BY ki.name, ug.name

Rhys

shree.pat18 · Answer 2 · 2015-01-22T11:26:02.077

Your question seems to suggest that you are only interested in getting the names of people who have images tagged to them in the other table. If that is the case, you can just retrieve the distinct names from the join which satisfy your filter condition, like so:

SELECT DISTINCT ki.name
FROM kantechimages AS ki
INNER JOIN user_group as UG ON ki.name like ug.name
WHERE ki.image_file is not null

If you do need to return the other fields as well, then you can try the below:

;with cte as
(
  SELECT ki.name kiname, ug.name ugname, ki.image_file ki_image_file, 
  row_number() over (partition by ki.name order by ug.name) rn
  FROM kantechimages AS ki
  INNER JOIN user_group as UG ON ki.name like ug.name
  WHERE ki.image_file is not null
 )

 select kiname, ugname, ki_image_file 
 from cte
 where rn = 1

Demo

Thanks for the help, and thanks for the example too! – Sam Jan 22 '15 at 11:35 — Sam, Jan 22 '15 at 11:35

score 0 · Answer 3 · edited May 23 '17 at 11:43

The reason why you are getting duplicates is that you are grouping using the image file, so it will return all the rows in Table KI with an image and a match to Table UG.

If you simply want to return a list of names where there is a match and returning the image file is not important, only that there is an image file, then the below will work:

SELECT distinct ki.name
FROM kantechimages AS ki
INNER JOIN user_group as UG ON ki.name = ug.name
WHERE ki.image_file is not null;

If you are looking for an exact match on the names then '=' is more efficient than 'LIKE' as it doesn't perform a pattern match. See this answer.

If the image is important, then you will need to decide the criteria for filtering out the names with multiple images in table KI. How you then write the query will depend on what columns/data you have available to create the filter.

How to remove duplicate entries? SQL Server 2008

3 Answers3