[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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