Solved

Find records that share same data

Posted on 2011-03-03
8
397 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 40

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 40

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 40

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 40

Accepted Solution

by:
Sharath earned 500 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

A short article about a problem I had getting the GPS LocationListener working.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

785 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