CODEWITHSUNDEEP
mysqlsql
CodeMonkey
CodeMonkey

Reputation: 1107

Show duplicate records for a SQL query

I am trying to see the duplicate records for an object over a week period. I am interested in seeing the duplicates, not objects that have had only a single instance. This is what I have written so far:

SELECT a.asset, t.ticketnum, t.symptom_mask, t.setsolution, t.`otherdesc`
    FROM lamarinfo AS a
    JOIN lfso AS t
    ON (a.id = t.asset_id)
    WHERE open_dt BETWEEN CURDATE() - INTERVAL 7 DAY AND SYSDATE()
GROUP BY a.`asset` HAVING COUNT(*) > 1;

This returns the records that are duplicate, but not each record for the duplicates. Any ideas?

Upvotes: 3

Views: 392

Answers (1)

Shawn Bower
Shawn Bower

Reputation: 1147

Right so you should be able to handle this with a subquery.

SELECT a.asset, t.ticketnum, t.symptom_mask, t.setsolution, t.`otherdesc`
   FROM lamarinfo AS a
   JOIN lfso AS t
   ON (a.id = t.asset_id)
   WHERE a.asset IN (SELECT asset FROM lamarinfo WHERE open_dt BETWEEN CURDATE() - INTERVAL 7 DAY AND SYSDATE() GROUP BY asset HAVING COUNT(*) > 1)

Upvotes: 5

Related Questions