CODEWITHSUNDEEP
mysqlsqlpostgresql
Ramiz Raja
Ramiz Raja

Reputation: 6030

How to avoid duplicate records in one-to-many association without using distinct?

I have two tables issues and time_entries and they have one-to-many association between them. We can created multiple time_entries for an issue.

here is some example data,

issue

id  |  subject  |  description
------------------------------
1   |  test     |  test
2   |  test1    |  test1
3   |  test2    |  test2

Time entries

id  | issue_id  |  hours | spent_on   | created_on
---------------------------------------------------
1   |  1        |  2     | 2016-12-23 | 2016-12-23
2   |  1        |  2     | 2016-12-23 | 2016-12-23
3   |  2        |  3     | 2016-12-23 | 2016-12-23
4   |  2        |  5     | 2016-12-23 | 2016-12-23
5   |  4        |  4     | 2016-12-23 | 2016-12-23

Now I want to fetch all the issues which have spent time after a particular date. 

SELECT *
FROM "issues"
INNER JOIN "time_entries" ON "time_entries"."issue_id" = "issues"."id"
WHERE time_entries.created_on > '2016-12-22'

It's returning multiple records for issues which have more than one entries.

id   | subject  | description
-----------------------------
1   |  test     |  test
1   |  test     |  test
2   |  test1    |  test1
2   |  test1    |  test1
3   |  test2    |  test2

How can I avoid these duplicate records without using distinct. I can't use distinct due to technical reasons in my application.

Any help will be highly appreciated.

Upvotes: 0

Views: 123

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

One option is to use SELECT DISTINCT:

SELECT DISTINCT t1.id,
                t1.subject,
                t1.description
FROM issues t1
INNER JOIN time_entries t2
    ON t2.issue_id = t1.id
WHERE t2.created_on > '2016-12-22'

If you can't use DISTINCT or GROUP BY, then one other option would be to use a subquery which aggregates over issues in the time_entries table and determines which issues meet the requirement. Something like this:

SELECT t1.id,
       t1.subject,
       t1.description
FROM issues t1
INNER JOIN
(
    SELECT issue_id
    FROM time_entries
    GROUP BY issue_id
    HAVING SUM(CASE WHEN created_on > '2016-12-22' THEN 1 ELSE 0 END) > 0
) t2
    ON t2.issue_id = t1.id

Upvotes: 2

Vijaya Vignesh Kumar
Vijaya Vignesh Kumar

Reputation: 444

SELECT * FROM "issues" 
INNER JOIN "time_entries" ON "time_entries"."issue_id" = "issues"."id"
WHERE time_entries.created_on > '2016-12-22' 
group by id

Upvotes: 0

Related Questions