SQL query to retrieve entries if userId matches table1’s employee_id or table2’s contributor_id

I’m trying to retrieve all kpi_id, kpi_name, employee_id, contributor_id from table1 and table2 if the provided userId matches employee_id from table1 or contributor_id from table2.

Sample table1:

id	kpi_name	employee_id
1	kpi1	5
2	kpi2	6
3	kpi3	9

Sample table2:

id	kpi_id	contributor_id
1	1	9
1	3	5
1	1	6

Now, if a given userId is 5 then as this user is the owner of kpi_id 1 and a contributor of kpi_id 3 the result should look like this:

My desired output:

kpi_id	kpi_name	employee_id	contributor_id
1	kpi1	5	5
3	kpi3	9	5

So far I’ve tried the following query:

const query = knex.select([
            't1.id as kpiId',
            't1.name as kpiName',
            't1.employee_id as employeeId',
            't2.contributor_id as contributorId'
        ]).from('table1 as t1')
            .leftJoin('table2 as t2', function () {
                this.on('t2.kpi_id', '=', 't1.id')
            })

           query.where({
                't1.employee_id': this._loggedInUser.id,
            }).orWhere(
                't2.contributor_id': this._loggedInUser.id,).orderBy('t1.id');

But, this returns duplicate entries if there are multiple contributors of the same kpi_id. My current SQL query generates this:

kpi_id	kpi_name	employee_id	contributor_id
1	kpi1	5	5
1	kpi1	5	6
3	kpi3	9	5

Answer

Adding distinct and groupBy fixed my issue:

const query = knex.select([
            't1.id as kpiId',
            't1.name as kpiName',
            't1.employee_id as employeeId',
            't2.contributor_id as contributorId'
        ]).from('table1 as t1')
            .leftJoin('table2 as t2', function () {
                this.on('t2.kpi_id', '=', 't1.id')
            }).groupBy('t1.id', 't1.name', 't1.employee_id', 't2.contributor_id')

           query.where({
                't1.employee_id': this._loggedInUser.id,
            }).orWhere(
                't2.contributor_id': this._loggedInUser.id,).orderBy('t1.id').distinctOn('t1.id');