joining views - 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

SQL Server 2005 Forums

Transact-SQL (2005)

joining views

Author

Topic

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-03-06 : 15:21:40

create view myidtitlenew
as
SELECT c1.messageid_,
c1.title_, c1.list_,
c1.created_
FROM outmail_ c1
inner join (Select max(messageid_) as messageid_, max(title_) as title_
FROM outmail_ c1 where list_ = 'copd'
GROUP BY substring([title_],1,charindex(' ',[title_])-1)
) c2

ON c1.messageid_ = c2.messageid_
go

create view mymembersum
as
select distinct list_, membertype_ , count(*) as cnt
from Members_ where list_='copd'
group by membertype_,list_
go

how could i join them?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-06 : 15:39:41

Maybe:

SELECT 
	*
FROM 
	myidtitlenew
INNER JOIN
	mymembersum
	ON myidtitlenew.list_ = mymembersum.list_

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-03-06 : 16:28:06

thanks lamprey

i did like this - same
select c1.messageid_,
c1.created_,c2.membertype_ ,count(*) as cnt from myidtitlenew c1
left outer join members_ c2
on c1.list_ = c2.list_
group by c2.membertype_,c1.messageid_,
c1.created_
order by c1.messageid_
71876 2007-08-08 10:40:56.300 held 72
71876 2007-08-08 10:40:56.300 normal 547
71876 2007-08-08 10:40:56.300 unsub 8
84675 2007-09-26 13:18:47.237 normal 547
84675 2007-09-26 13:18:47.237 held 72
84675 2007-09-26 13:18:47.237 unsub 8
94592 2007-10-24 10:48:08.077 unsub 8
94592 2007-10-24 10:48:08.077 held 72
94592 2007-10-24 10:48:08.077 normal 547
108740 2007-11-28 14:42:55.780 held 72
108740 2007-11-28 14:42:55.780 unsub 8
108740 2007-11-28 14:42:55.780 normal 547
150430 2008-01-22 15:41:24.523 unsub 8
150430 2008-01-22 15:41:24.523 normal 547
150430 2008-01-22 15:41:24.523 held 72
180618 2008-02-27 10:19:50.547 held 72
180618 2008-02-27 10:19:50.547 normal 547
180618 2008-02-27 10:19:50.547 unsub 8
181708 2008-02-28 11:39:39.873 normal 547
181708 2008-02-28 11:39:39.873 held 72
181708 2008-02-28 11:39:39.873 unsub 8
182133 2008-02-28 21:15:38.457 held 72
182133 2008-02-28 21:15:38.457 unsub 8
182133 2008-02-28 21:15:38.457 normal 547
183625 2008-03-01 10:10:25.343 held 72
183625 2008-03-01 10:10:25.343 normal 547
183625 2008-03-01 10:10:25.343 unsub 8

can it possible to get one row for eeach messageid..messageid should not be repeat 3times..i know it should me as membertype for each messageid is different-3..but still is there anyway? or solution for that..

thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 16:35:27

you would remove the membertype from the select and the group by.


select c1.messageid_,c1.created_,count(*) as cnt from myidtitlenew c1
left outer join members_ c2 on c1.list_ = c2.list_
group by c1.messageid_,c1.created_ 
order by c1.messageid_

Poor planning on your part does not constitute an emergency on my part.

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-03-07 : 08:05:15

but in output i want membertype..

how can iget like this way?

messageid date held normal unsub
----------------------------------------
71876 2007-08-08 10:40:56.300 72 547 8
84675 2007-09-26 13:18:47.237 72 547 8

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-03-07 : 09:08:56

select distinct m1.list_, a.membertype_ as Normal,b.membertype_ as Held,c.membertype_ as Unsub,
d.membertype_ as Other,count(*) as cnt
from Members_ m1
left outer join (select distinct membertype_ from members_ where membertype_ like 'n%') a
on a.membertype_ = m1.MemberType_
left outer join (select distinct membertype_ from members_ where membertype_ like 'h%') b
on b.membertype_ = m1.MemberType_
left outer join (select distinct membertype_ from members_ where membertype_ like 'u%') c
on c.membertype_ = m1.MemberType_
left outer join (select distinct membertype_ from members_ where membertype_ like 'o%') d
on d.membertype_ = m1.MemberType_

where m1.list_='copd'
group by a.membertype_, b.membertype_,c.membertype_,d.membertype_,m1.list_
go

list normal held unsub other cnt
---------------------------------
copd NULL NULL unsub NULL 8
copd NULL held NULL NULL 72
copd normal NULL NULL NULL 547

i tried like:
select distinct m1.list_, a.cnt as Normal,b.cnt as Held,c.cnt as Unsub,
d.cnt as Other
from Members_ m1
left outer join (select distinct count(*) as cnt,membertype_,list_ from members_ where membertype_ like 'n%' group by membertype_,list_) a
on a.membertype_ = m1.MemberType_ and a.list_ = m1.list_
left outer join (select distinct count(*) as cnt,membertype_,list_ from members_ where membertype_ like 'h%' group by membertype_,list_) b
on b.membertype_ = m1.MemberType_ and b.list_ = m1.list_
left outer join (select distinct count(*) as cnt,membertype_,list_ from members_ where membertype_ like 'u%' group by membertype_,list_) c
on c.membertype_ = m1.MemberType_ and c.list_ = m1.list_
left outer join (select distinct count(*) as cnt,membertype_,list_ from members_ where membertype_ like 'o%' group by membertype_,list_) d
on d.membertype_ = m1.MemberType_ and d.list_ = m1.list_

where m1.list_='copd'

list normal held unsub other
---------------------------------
copd NULL NULL 8 NULL
copd NULL 72 NULL NULL
copd 547 NULL NULL NULL

but i want like:

list normal held unsub other
---------------------------------
copd 547 72 8 0

can anyone help me plz?

thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 09:52:12

quote:
Originally posted by ri16

but in output i want membertype..

how can iget like this way?

messageid date held normal unsub
----------------------------------------
71876 2007-08-08 10:40:56.300 72 547 8
84675 2007-09-26 13:18:47.237 72 547 8

Okay, where is membertype int that sample output?

My understanding was the membertype caused multiple rows, which was the problem ...

your sample here:
select distinct m1.list_, a.cnt as Normal,b.cnt as Held,c.cnt as Unsub,
d.cnt as Other
from Members_ m1
left outer join (select distinct count(*) as cnt,membertype_,list_ from members_ where membertype_ like 'n%' group by membertype_,list_) a
on a.membertype_ = m1.MemberType_ and a.list_ = m1.list_
left outer join (select distinct count(*) as cnt,membertype_,list_ from members_ where membertype_ like 'h%' group by membertype_,list_) b
on b.membertype_ = m1.MemberType_ and b.list_ = m1.list_
left outer join (select distinct count(*) as cnt,membertype_,list_ from members_ where membertype_ like 'u%' group by membertype_,list_) c
on c.membertype_ = m1.MemberType_ and c.list_ = m1.list_
left outer join (select distinct count(*) as cnt,membertype_,list_ from members_ where membertype_ like 'o%' group by membertype_,list_) d
on d.membertype_ = m1.MemberType_ and d.list_ = m1.list_

where m1.list_='copd'

list normal held unsub other
---------------------------------
copd NULL NULL 8 NULL
copd NULL 72 NULL NULL
copd 547 NULL NULL NULL

 

If you did INNER JOIN and GROUP BY it work as you need.

below:
[code]
select distinct m1.list_,
				a.cnt as Normal,
				b.cnt as Held,
				c.cnt as Unsub,
d.cnt as Other
from Members_ m1
INNER join (select distinct count(*) as cnt,
						membertype_,list_ 
						from members_ where membertype_ like 'n%' 
						group by membertype_,list_) a
on a.membertype_ = m1.MemberType_ and a.list_ = m1.list_
INNER join (select distinct count(*) as cnt,
						membertype_,
						list_ from members_ 
			where membertype_ like 'h%' 
			group by membertype_,list_) b
on b.membertype_ = m1.MemberType_ and b.list_ = m1.list_
inner join (select distinct count(*) as cnt,
					membertype_,list_ 
					from members_ 
					where membertype_ like 'u%' 
					group by membertype_,list_) c
on c.membertype_ = m1.MemberType_ and c.list_ = m1.list_
inner join (select distinct count(*) as cnt,
					membertype_,list_ 
					from members_ 
					where membertype_ like 'o%' 
					group by membertype_,list_) d
on d.membertype_ = m1.MemberType_ and d.list_ = m1.list_

where m1.list_='copd'

group by m1.list_

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources