INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Retrieve duplicate records by date time

Retrieve duplicate records by date time

(OP)
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

RE: Retrieve duplicate records by date time

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?

RE: Retrieve duplicate records by date time

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".)

RE: Retrieve duplicate records by date time

(OP)
Hi, PHV and JarlH

thanks u so mush both of you for solution. bigsmile

thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close