Retrieve duplicate records by date time 2

[soushi01]

Hi

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

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]

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

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
FAQ181-2886

 

[JarlH]

And an EXISTS solution:

[tt]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)[/tt]

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]

Hi, PHV and JarlH

thanks u so mush both of you for solution.

thanks