Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Find records that share same data

Posted on 2011-03-03
8
Medium Priority
?
408 Views
Last Modified: 2012-05-11
Hello:
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
                           EventDate

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,
0
Comment
Question by:gloriagalvez
  • 4
  • 3
8 Comments
 
LVL 24

Expert Comment

by:jimyX
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';
0
 
LVL 41

Expert Comment

by:Sharath
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?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35034788
you can try this query.
SELECT * 
  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

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:gloriagalvez
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.
0
 
LVL 41

Expert Comment

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

Author Comment

by:gloriagalvez
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

GG
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35051614
try this
SELECT * 
  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

0
 

Author Closing Comment

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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Starting up a Project
Loops Section Overview
Suggested Courses

782 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