Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Retrieve duplicate records by date time 2

Status
Not open for further replies.

soushi01

Technical User
Dec 27, 2010
31
MY
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
 
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
FAQ181-2886
 
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".)
 
Hi, PHV and JarlH

thanks u so mush both of you for solution. [bigsmile]

thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top