Reputation: 1
Please need help to solve this issue I have a table Meeting
PersonOne PersonTwo
Ajay Bharat
Ajay Elas
Chetan Deepak
Deepak Chetan
Chetan Deepak
Farooq Ajay
Anil Sanjay
Gaganan Hassan
Hassan Isqh
Output should be
PersonOne PersonTwo
Anil Sanjay
If user wants to show only persons who have attended meeting only once here Bharat as attanded meeting only once but he had attended meeting with ajay, who had attended the meeting more then once. so Bharat should not show in the list
Output should be
PersonOne PersonTwo
Chetan Deepak
Deepak Chetan
Chetan Deepak
If user wants to show only persons who have attended meeting for third time
I have tried almost all possible statements , like ,ROW_NUMBER() ,RANK() ,DENSE_RANK() ,NTILE
but result..plz need your help
Upvotes: 0
Views: 77
Reputation: 702
Please try the below query
SELECT Person
INTO #TEMP
FROM
(
SELECT PersonOne as Person FROM tblMeeting
UNION ALL
SELECT PersonTwo as Person FROM tblMeeting
) P
GROUP By Person
Having Count(Person) =1
select * from tblMeeting where
PersonOne in ( select Person from #TEMP) and
PersonTwo in ( select Person from #TEMP)
Upvotes: 1
Reputation: 1901
The query below will take some time - no doubt, but definitely it will give you expected result.
SELECT * FROM tblMeeting WHERE (PersonOne IN
(
SELECT PersonOne FROM
(
SELECT PersonOne, COUNT(*) AS Expr1 FROM
(
SELECT PersonOne FROM tblMeeting AS tblMeeting_3
UNION ALL
SELECT PersonTwo FROM tblMeeting AS tblMeeting_1
) AS derivedtbl_1 GROUP BY PersonOne
) AS derivedtbl_2 WHERE (Expr1 = 1)
))
AND (PersonTwo IN
(
SELECT PersonOne FROM
(
SELECT PersonOne, COUNT(*) AS Expr1 FROM
(
SELECT PersonOne FROM tblMeeting AS tblMeeting_2
UNION ALL
SELECT PersonTwo FROM tblMeeting AS tblMeeting_1
) AS derivedtbl_1_1 GROUP BY PersonOne
) AS derivedtbl_2_1 WHERE (Expr1 = 1)
))
Here is Fiddle http://sqlfiddle.com/#!2/51b91/4
P.S : You can improve the query performance. :)
Upvotes: 1