February 12, 2013 at 11:48 am
Create table collapselist
( mnbr int,
Pid int,
Pname varchar(10),
Jid int,
Jname varchar(10))
Mnbr Pid Pname Jid Jname
------------------------------------------------
150 1 Mark 8 Jerry
250 1 Mark 8 Jerry
350 2 Jim 9 Luke
450 2 Jim 9 Luke
550 2 Jim 10 Jude
650 3 Andy 11 Matt
750 4 Brian 21 Chris
850 4 Brian 7 Mac
950 5 Jean 21 Chris
For the above data..... My output should be as shown below:
350 2 Jim 9 Luke
450 2 Jim 9 Luke
550 2 Jim 10 Jude
750 4 Brian 21 Chris
850 4 Brian 7 Mac
In the case of mnbr 150 n 250.... The primary owner mark has two joint accounts. N on both the joint accounts the joint owner is jerry. So it should not be displayed.
In the case if mnbr: 350, 450, 550
Jim has 3 joint accounts. And all the three joint accounts dont belong to the same joint owner... So all the 3 hav to be displayed.
Mnbr 650 has only obe joint account with one joint owner.... So it should not be displayed.
this is the input query:
DECLARE @table TABLE( mnbr int,
Pid int,
Pname varchar(10),
Jid int,
Jname varchar(10))
INSERT INTO @table
VALUES(150 , 1 , 'Mark' , 8 ,'Jerry')
,(250 , 1 , 'Mark' , 8 , 'Jerry')
,(350 , 2 , 'Jim' , 9 , 'Luke')
,(450 , 2 , 'Jim' , 9 , 'Luke')
,(550 , 2 , 'Jim' , 10 , 'Jude')
,(650 , 3 , 'Andy' , 11 , 'Matt')
,(750 , 4 , 'Brian' , 21 , 'Chris')
,(850 , 4 , 'Brian' , 7 , 'Mac')
,(950 , 5 , 'Jean ' , 21 , 'Chris')
SELECT * FROM @table
February 12, 2013 at 1:21 pm
SELECT *
FROM @Table T
WHERE PID IN (SELECT PID FROM @table
GROUP BY PID
HAVING COUNT(PID) > 1
AND COUNT(DISTINCT JID) > 1)
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply