Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

Find records that share same data

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
gloriagalvez
Asked:
gloriagalvez
  • 4
  • 3
1 Solution
 
jimyXCommented:
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
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
gloriagalvezAuthor Commented:
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
 
SharathData EngineerCommented:
Then why do you ant EventPartner Y in the expected result? Can you post your expected result?
0
 
gloriagalvezAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
gloriagalvezAuthor Commented:
Thank you, It did work very well.!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now