Retrieve duplicate records by date time
2

Question

Retrieve duplicate records by date time

soushi01 (TechnicalUser)

(OP)

4 Sep 12 11:44

Hi

I need to create crash time table report, but have no idea how to retrieve the duplicate record... hammer

Here is CLASS_TIME_TABLE table
--------------------------------------------------------------------------------------------------------------------
NAME--------Day----------Date------------Start_time-----End_time----Subject-------------------Classroom
--------------------------------------------------------------------------------------------------------------------
Jordan------Monday------3-sep-2012----10:00am-----12:00pm------Business Management----level1a
Jordan------Monday------3-sep-2012----10:00am-----12:00pm------Math------------------------level1b
Jordan------Monday------3-sep-2012----1:00pm------3:00pm--------English Studies-------------level1c
Billy---------Monday------3-sep-2012----10:00am-----12:00pm------Business Management----level1a
Stacy-------Monday------3-sep-2012----10:00am-----12:00pm------Math------------------------level1b
Stacy-------Tuesday------4-sep-2012----11:00am-----1:00pm-------Business Law--------------level1b
Stacy-------Tuesday------4-sep-2012----11:00am-----1:00pm-------Marketing------------------level1c
Tony--------Wednesday--4-sep-2012----9:00am-----11:00am-------Business Law--------------level1a
Tony--------Wednesday--4-sep-2012----9:00am-----11:00am-------Marketing------------------level1d

But how to retrieve like this (expected result)

--------------------------------------------------------------------------------------------------------------------
NAME--------Day----------Date------------Start_time-----End_time----Subject-------------------Classroom
--------------------------------------------------------------------------------------------------------------------
Jordan------Monday------3-sep-2012----10:00am-----12:00pm------Business Management----level1a
Jordan------Monday------3-sep-2012----10:00am-----12:00pm------Math------------------------level1b
Stacy-------Tuesday------4-sep-2012----11:00am-----1:00pm-------Business Law--------------level1b
Stacy-------Tuesday------4-sep-2012----11:00am-----1:00pm-------Marketing------------------level1c
Tony--------Wednesday--4-sep-2012----9:00am-----11:00am-------Business Law--------------level1a
Tony--------Wednesday--4-sep-2012----9:00am-----11:00am-------Marketing------------------level1d

regards,
Leong

PHV (MIS) · Answer 1 · 2012-09-04 12:11:18

A starting point (you have to deal with your columns having reserved name ...)

CODE

SELECT A.NAME,A.Day,A.Date,A.Start_time,A.End_time,A.Subject,A.Classroom
FROM CLASS_TIME_TABLE A INNER JOIN (
SELECT NAME,Date,Start_time,End_time FROM CLASS_TIME_TABLE
GROUP BY NAME,Date,Start_time,End_time HAVING COUNT(*)>1
) B ON A.NAME=B.NAME AND A.Date=B.Date AND A.Start_time=B.Start_time AND A.End_time=B.End_time

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

JarlH (Programmer) · Answer 2 · 2012-09-05 03:22:04

And an EXISTS solution:

SELECT *
FROM CLASS_TIME_TABLE A
WHERE EXISTS (
SELECT * FROM CLASS_TIME_TABLE B
WHERE A.NAME = B.NAME
AND A.Date = B.Date
AND A.Start_time = B.Start_time
AND A.End_time = B.End_time
GROUP BY NAME, Date, Start_time, End_time
HAVING COUNT(*) > 1)

Core SQL-99.

(Except that DATE is an invalid column name since it's a reserved word. Change or double quote, i.e. "Date".)

soushi01 (TechnicalUser) · Answer 3 · 2012-09-11 11:12:17

Hi, PHV and JarlH

thanks u so mush both of you for solution. bigsmile

thanks

Come Join Us!

Posting Guidelines

Retrieve duplicate records by date time
2