Oracle APEX interactive Grid displays too many duplicates

Question

I have 3 tables: Employees, Locations, and EmpLocation

Employees stores employee data with Employee_ID as primary key,
Locations stores location data with Location_ID.
EmpLocation stores Employee_ID and Location_ID. Each Employee can have multiple locations.

I have created an Interactive Grid that is based on the data from EmpLocation table:

SELECT EMPLOYEE_ID, LOCATION_ID
FROM EmpLocation
GROUP BY EMPLOYEE_ID, LOCATION_ID

Then each of the columns I set as a select list and use SQL query as a source:

SELECT Employee_FName, Employee_ID FROM Employees

and

SELECT LocationName, Location_ID FROM Locations

Everything looks fine but when I run query

SELECT EMPLOYEE_ID, LOCATION_ID
FROM EmpLocation
GROUP BY EMPLOYEE_ID, LOCATION_ID

in TOAD, I get about 100 records; but in the grid I get many duplicates. Cannot figure out what is going on...

Littlefoot Littlefoot · Accepted Answer · 2018-09-14T06:30:01

Why do you allow duplicates in the EmpLocation table at the first place? What would be the point in keeping

Littlefoot        Croatia        --> this ...
Coding Duchess    Somewhere
Littlefoot        Croatia        --> ... and this are duplicates

Create unique index

create unique index ui_emploc on emplocation (employee_id, location_id);

Doing so, you'd avoid group by clause you currently use. Though, why do you use it? What's wrong with usual way of getting distinct values, i.e.

select distinct employee_id, location_id
from emplocation;

As of Apex & its Interactive Grid joys of all kinds (although it looks pretty, I still prefer tabular form over it): what happens if you create a classic report (instead of an interactive grid) - do you still see duplicates?

Oracle APEX interactive Grid displays too many duplicates

2 Answers