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!

Register Log in
  • Congratulations Rhinorhino 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

soushi01

Technical User
Joined
Dec 27, 2010
Messages
31
Location
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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".)
 
Upvote 0 Downvote
Hi, PHV and JarlH

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

thanks
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question Question
Count records until a record value changes
Replies
2
Views
818
carp
carp
sal21
  • Locked
  • Question Question
SORT by part of filed
Replies
5
Views
782
2009luca
2009luca
Tetol
  • Locked
  • Question Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
587
carp
carp
jchewsmith
  • Locked
  • Question Question
Update Query with a MAX(Date) subquery
Replies
1
Views
688
JarlH
JarlH
swhiteside
  • Locked
  • Question Question
Max Date By Training code - Crystal XI
Replies
3
Views
603
PHV
PHV

Part and Inventory Search

Sponsor

Back
Top