Query distinc on multiple columns of a database

Question

I'm building an app using C# and an Access database where there's a list of employees and their bosses. I looks a bit like this:

[Employee name] [Boss #1 name] [Boss #2 name]
     John           Jake         Margaret
     Jane           Jake         Tony    
     Bob            Mike         Tony
     Marty          Mike         Margaret

Is it possible to query the database to display the every names without duplicates, like this.

John
Jane
Bob
Marty
Jake
Mike
Margaret
Tony

Thanks a lot !

You need to Select Distinct from all the tables if you only want their names. I'm not sure how to do it in Access, but here's a start: http://stackoverflow.com/questions/15310782/how-to-select-distinct-value-from-multiple-tables — Brandon, Jan 21 '14 at 20:08
Why don't you have a table of Employees who are referenced by the bosses? Then you can just pull a query of the Employee table. — enderland, Jan 21 '14 at 20:10
These are all from the same table, it's only different columns. — ElCapitaine, Jan 21 '14 at 20:11

score 1 · Answer 1 · answered Jan 21 '14 at 20:42

You should really look into changing you DB Schema. It looks like you are not using proper relational techniques.

Anyhow...a solution would be to union each columns and select distinct.

SELECT DISTINCT n 
FROM   (SELECT employee_name AS n 
        FROM   table_1 
        UNION 
        SELECT boss_1_name AS n 
        FROM   table_1 
        UNION 
        SELECT boss_2_name AS n 
        FROM   table_1) names;

This is a quick solution which will work now, but long term you should really look into changing your schema.

score 0 · Accepted Answer · answered Jan 21 '14 at 20:52

If you're just trying to run a query against some existing Access database you have then:

A SQL Query for this would be (keep in mind that UNION already does a distinct):

SELECT [Employee Name] FROM [tablename]
UNION
SELECT [Boss #1 Name] FROM [tablename]
UNION
SELECT [Boss #2 Name] FROM [tablename]

I think you can run SQL in Access in a tab in the query creator called SQL view. You can also check how to do UNION in Access, since this is what you want to use.

If you are planning to make this a long lasting thing, then what you could do is create two tables:

[Employee]
  -ID (primary key identity)
  -Name

[EmployeeSupervisor]
  -EmployeeId (foreign key to Employee.ID) (part of PK)
  -SupervisorId (foreign key to Employee.ID) (part of PK)

or if every employee has exactly two bosses then you can do:

[Employee]
  -ID (primary key identity)
  -Name

[EmployeeSupervisor]
  -EmployeeId (foreign key to Employee.ID) (PK)
  -Supervisor1Id (foreign key to Employee.ID)
  -Supervisor2Id (foreign key to Empoyee.ID)

Query distinc on multiple columns of a database

2 Answers2