How to get the data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get the data

Author

Topic

pankaj2910
Starting Member

31 Posts

Posted - 2014-05-06 : 21:59:20

We have two tables :
Table 1:
ID Name EID Code Phone PinCode
1 | ABC | 0143 | 9012 | 9765423 |208765
1 | ABC | 0143 | 9012 | 9872123 |208765
2 | BCD | 9871 | 9654 | 7654321 |901234
3 | bgd | 7654 | 1654 | 7654231 |877623
3 | bgd | 7654 | 8762 | 7654231 |877123

Table 2:
ID MemID Name Gender Age Relation
1 | 1 | ABC | M |65 | Self
1 | 3 | KHG | F |15 | Daughter
1 | 2 | AGD | F |45 | Spouse
1 | 1 | ABC | M |60 | Self
1 | 4 | ABC | M |25 | Son
2 | 2 | BCD | F |50 | Spouse
2 | 4 | BWR | F |30 | Daughter
3 | 1 | bgd | M |45 | Self
3 | 2 | GKD | F |35 | Spouse
3 | 3 | KMS | M |20 | Son
3 | 1 | bgd | M |45 | Self
3 | 2 | GKD | F |35 | Spouse
3 | 3 | KMS | M |20 | Son

I need a result like

ID Name EID Code Phone PinCode MemID Name Gender Age Relation
1 | ABC| 0143|9012|9765423|208765|1 | ABC | M |65 | Self
1 -------------------------------| 3 | KHG | F |15 | Daughter
1 -------------------------------| 2 | AGD | F |45 | Spouse
2 | BCD| 9871|9654|7654321|901234| 2 | BCD | F |50 | Spouse
2 -------------------------------| 4 | BWR | F |30 | Daughter
3 | bgd| 7654|1654|7654231|877623| 1 | bgd | M |45 | Self
3 -------------------------------| 2 | GKD | F |35 | Spouse
3 -------------------------------| 3 | KMS | M |20 | Son

Please help me out to find the data.
I also want the count in table 2 on distinct memid against each ID
like ID 1 has total 5 rows but after neglecting duplicate memid its total is 3 so in table two total memid is 8 after neglecting duplicate memid

pankajrocks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-07 : 03:57:32

what happen to this ?
1 | 4 | ABC | M |25 | Son

KH
[spoiler]Time is always against us[/spoiler]

pankaj2910
Starting Member

31 Posts

Posted - 2014-05-07 : 04:21:52

I don't want this

pankajrocks

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-05-07 : 04:45:57

[code]
declare @Table1 table(ID int, Name varchar(3), EID varchar(4), Code varchar(4), Phone varchar(10), PinCode varchar(10))
insert into @Table1 select
1 , 'ABC' , '0143' , '9012' , '9765423' ,'208765' union all select
1 , 'ABC' , '0143' , '9012' , '9872123' ,'208765' union all select
2 , 'BCD' , '9871' , '9654' , '7654321' ,'901234' union all select
3 , 'bgd' , '7654' , '1654' , '7654231' ,'877623' union all select
3 , 'bgd' , '7654' , '8762' , '7654231' ,'877123'

declare @Table2 table(ID int, MemID int, Name varchar(3), Gender varchar, Age int, Relation varchar(10))
insert into @Table2 select
1 , 1 , 'ABC' , 'M' ,65 , 'Self' union all select
1 , 3 , 'KHG' , 'F' ,15 , 'Daughter' union all select
1 , 2 , 'AGD' , 'F' ,45 , 'Spouse' union all select
1 , 1 , 'ABC' , 'M' ,60 , 'Self' union all select
1 , 4 , 'ABC' , 'M' ,25 , 'Son' union all select
2 , 2 , 'BCD' , 'F' ,50 , 'Spouse' union all select
2 , 4 , 'BWR' , 'F' ,30 , 'Daughter' union all select
3 , 1 , 'bgd' , 'M' ,45 , 'Self' union all select
3 , 2 , 'GKD' , 'F' ,35 , 'Spouse' union all select
3 , 3 , 'KMS' , 'M' ,20 , 'Son' union all select
3 , 1 , 'bgd' , 'M' ,45 , 'Self' union all select
3 , 2 , 'GKD' , 'F' ,35 , 'Spouse' union all select
3 , 3 , 'KMS' , 'M' ,20 , 'Son'

;with distinct_table_1 as (
select ROW_NUMBER() over (partition by id order by (select 1))rn, * from @Table1
), distinct_table_2 as (
select ROW_NUMBER() over (partition by id, MemID order by (select 1))rn, * from @Table2
), formatting as(
select ROW_NUMBER() over (partition by a.id order by b.MemID)rn, a.id, a.Name, a.EID, a.Code, a.PinCode, b.MemID, b.Name as bname, b.Gender, b.Age, b.Relation
from distinct_table_1 a
left join distinct_table_2 b
on a.ID = b.ID
where a.rn = 1
and b.rn = 1
)
select
id,
Name = case when rn > 1 then '-' else Name end,
EID = case when rn > 1 then '-' else EID end,
Code = case when rn > 1 then '-' else Code end,
PinCode = case when rn > 1 then '-' else PinCode end,
MemID,
Name = bname,
Gender,
Age,
Relation
from formatting
where not (id = 1 and MemID = 4)
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-07 : 04:52:10

quote:
Originally posted by pankaj2910

I don't want this

pankajrocks

Why ?

KH
[spoiler]Time is always against us[/spoiler]

pankaj2910
Starting Member

31 Posts

Posted - 2014-05-07 : 04:59:11

B'coz this value comes in the duplicate set of data against ID =1

pankajrocks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-07 : 05:11:52

quote:
Originally posted by pankaj2910

B'coz this value comes in the duplicate set of data against ID =1

pankajrocks

when you say "Duplicate", what are the columns you consider ?

KH
[spoiler]Time is always against us[/spoiler]

pankaj2910
Starting Member

31 Posts

Posted - 2014-05-07 : 05:32:17

only two ID & MemID for example in first set we have ID =1 in which first 3 rows having that data now when we have ID = 1 but the data affect in next 2 rows so we want to neglect those records we only consider first 3, the same will happen in all the data.

pankajrocks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-07 : 07:10:56

based on what condition that you will want to ignore the last 2 rows ? And only consider the first 3 rows

1 | 1 | ABC | M |65 | Self
1 | 3 | KHG | F |15 | Daughter
1 | 2 | AGD | F |45 | Spouse
1 | 1 | ABC | M |60 | Self
1 | 4 | ABC | M |25 | Son

KH
[spoiler]Time is always against us[/spoiler]

pankaj2910
Starting Member

31 Posts

Posted - 2014-05-07 : 14:31:49

Dear Waterduck,
thanks for helping me but the query not executed on huge data.
Here I m just sharing example, I've huge data in which this type of result.

quote:

Originally posted by waterduck


declare @Table1 table(ID int, Name varchar(3), EID varchar(4), Code varchar(4), Phone varchar(10), PinCode varchar(10))
insert into @Table1 select
1 , 'ABC' , '0143' , '9012' , '9765423' ,'208765' union all select
1 , 'ABC' , '0143' , '9012' , '9872123' ,'208765' union all select
2 , 'BCD' , '9871' , '9654' , '7654321' ,'901234' union all select
3 , 'bgd' , '7654' , '1654' , '7654231' ,'877623' union all select
3 , 'bgd' , '7654' , '8762' , '7654231' ,'877123'

declare @Table2 table(ID int, MemID int, Name varchar(3), Gender varchar, Age int, Relation varchar(10))
insert into @Table2 select
1 , 1 , 'ABC' , 'M' ,65 , 'Self'	union all select
1 , 3 , 'KHG' , 'F' ,15 , 'Daughter'	union all select
1 , 2 , 'AGD' , 'F' ,45 , 'Spouse'	union all select
1 , 1 , 'ABC' , 'M' ,60 , 'Self'	union all select
1 , 4 , 'ABC' , 'M' ,25 , 'Son'		union all select
2 , 2 , 'BCD' , 'F' ,50 , 'Spouse'	union all select
2 , 4 , 'BWR' , 'F' ,30 , 'Daughter'	union all select
3 , 1 , 'bgd' , 'M' ,45 , 'Self'	union all select
3 , 2 , 'GKD' , 'F' ,35 , 'Spouse'	union all select
3 , 3 , 'KMS' , 'M' ,20 , 'Son'		union all select
3 , 1 , 'bgd' , 'M' ,45 , 'Self'	union all select
3 , 2 , 'GKD' , 'F' ,35 , 'Spouse'	union all select
3 , 3 , 'KMS' , 'M' ,20 , 'Son'

;with distinct_table_1 as (
	select ROW_NUMBER() over (partition by id order by (select 1))rn, * from @Table1
), distinct_table_2 as (
	select ROW_NUMBER() over (partition by id, MemID order by (select 1))rn, * from @Table2
), formatting as(
	select ROW_NUMBER() over (partition by a.id order by b.MemID)rn, a.id, a.Name, a.EID, a.Code, a.PinCode, b.MemID, b.Name as bname, b.Gender, b.Age, b.Relation
	from distinct_table_1 a
	left join distinct_table_2 b
	on a.ID = b.ID
	where a.rn = 1
	and b.rn = 1
)
select 
	id,
	Name = case when rn > 1 then '-' else Name end,
	EID = case when rn > 1 then '-' else EID end,
	Code = case when rn > 1 then '-' else Code end,
	PinCode = case when rn > 1 then '-' else PinCode end,
	MemID,
	Name = bname,
	Gender,
	Age,
	Relation	
from formatting
where not (id = 1 and MemID = 4)

pankajrocks

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-05-08 : 20:01:34

i think you better of answering khtan question before i proceed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources