Find records that share same data

Posted on 2011-03-03
Last Modified: 2012-05-11
I need to find eery event where an event partner X assisted to an event on the same day with the same person.  My resultset should be:
PersonID  EventPartner  EventID  Event Date
1                     X                 GH        1/1/2011
1                     X                  AB        1/1/2011
My tables are:
PersonTable   EventDetail
PersonID            EventID
EventID               EventPartner

My tables data is as follows:
Person Table
PersonID           EventID
1                           AB
2                           BC
3                            CD
6                            DE
3                            EF
6                            FG
1                            GH
8                            HI
2                            IJ
EventDetail Table
EventID          EventPartner     EventDate
GH                    X                      1/1/2011
AB                     X                     1/1/2011
HI                        Y                    3/3/2011
CD                     N                       5/1/2011
EF                        N                     5/1/2011

The last 2 records should not be in the resultset because they don't share the same personId.

Thank you,
Question by:gloriagalvez
  • 4
  • 3
LVL 24

Expert Comment

ID: 35034199
try this one please:
Select P.PersonID, E.EventPartner,  E.EventID,  E.EventDate From Person P, EventDetail E where P.EventID=E.EventID and E.EventPartner = 'X';
LVL 40

Expert Comment

ID: 35034764
In your example both CD and EF are sharing the same PersonID 3. Why don't you want those records in the final result set?
LVL 40

Expert Comment

ID: 35034788
you can try this query.
  FROM EventDetail 
 WHERE EventPartner IN (  SELECT ed.EventPartner 
                            FROM EventDetail AS ed 
                                 JOIN Person AS p 
                                   ON ed.EventID = p.EventID 
                        GROUP BY ed.EventPartner 
                          HAVING MAX(ed.EventDate) = MIN(ed.EventDate) 
                                 AND MAX(p.PersonID) = MIN(p.PersonID));

Open in new window


Author Comment

ID: 35044065
Hello Sharath:

Thank you for your response. X is only a filter I need where eventpartner = 'X'

I tried your solution but did not work.

Please help.
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

LVL 40

Expert Comment

ID: 35044734
Then why do you ant EventPartner Y in the expected result? Can you post your expected result?

Author Comment

ID: 35049939
Hello Sharath:

Thank you for your help.. The expected result is here:
PersonID  EventPartner  EventID  Event Date
1                     X                 GH        1/1/2011
1                     X                  AB        1/1/2011

LVL 40

Accepted Solution

Sharath earned 500 total points
ID: 35051614
try this
  FROM EventDetail 
 WHERE EventPartner IN (SELECT ed.EventPartner 
                          FROM EventDetail ed 
                               JOIN Person p 
                                 ON ed.EventID = p.EventID 
                         WHERE ed.EventPartner = 'X' 
                        HAVING COUNT(DISTINCT ed.EventDate) = 1 
                               AND COUNT(DISTINCT p.PersonID) = 1);

Open in new window


Author Closing Comment

ID: 35063378
Thank you, It did work very well.!!

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now