JOIN HELP - 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 2000 Forums

SQL Server Development (2000)

JOIN HELP

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2003-06-26 : 09:27:16

Below is a query that does a pivot on type by user.

The query works fine but, if you notice I have 4 users but, the group by only shows the users that have events. How can I show all users even without the user having any events.

Thanks

SET NOCOUNT ON
DECLARE @user TABLE ( uid bigint, uname varchar(25) )
DECLARE @evtypes TABLE ( evtype int, evame varchar(25) )
DECLARE @events TABLE ( evid bigint, uid bigint, evtype int )

INSERT INTO @user VALUES ( 1, 'Sean')
INSERT INTO @user VALUES ( 2, 'Jason')
INSERT INTO @user VALUES ( 3, 'Mary')
INSERT INTO @user VALUES ( 4, 'Mike')
INSERT INTO @evtypes VALUES ( 1, 'Closing 1')
INSERT INTO @evtypes VALUES ( 2, 'Closing 2')
INSERT INTO @evtypes VALUES ( 3, 'Closing 3')
INSERT INTO @events VALUES ( 1, 1,1 )
INSERT INTO @events VALUES ( 2, 1,2 )
INSERT INTO @events VALUES ( 3, 2,2 )
INSERT INTO @events VALUES ( 4, 2,2 )
INSERT INTO @events VALUES ( 5, 2,3 )
INSERT INTO @events VALUES ( 6, 3,1 )
INSERT INTO @events VALUES ( 7, 3,3 )

------------------------------------------------------------------------GET ALL EVENTS
SELECT
a.uname as closer,
SUM ( CASE WHEN b.evtype = 1 THEN 1 ELSE 0 END) as Closing1,
SUM ( CASE WHEN b.evtype = 2 THEN 1 ELSE 0 END) as Closing2,
SUM ( CASE WHEN b.evtype = 3 THEN 1 ELSE 0 END) as Closing3
FROM @user a
LEFT JOIN @events b ON (a.uid = b.uid)
INNER JOIN @evtypes c ON ( b.evtype = c.evtype)
GROUP BY a.uname
ORDER BY a.uname

slow down to move faster...

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-26 : 09:44:11

I can't see what you're acheiving by having the INNER JOIN in your query :


SELECT 
a.uname as closer, 
SUM ( CASE WHEN b.evtype = 1 THEN 1 ELSE 0 END) as Closing1, 
SUM ( CASE WHEN b.evtype = 2 THEN 1 ELSE 0 END) as Closing2, 
SUM ( CASE WHEN b.evtype = 3 THEN 1 ELSE 0 END) as Closing3 
FROM @user a 
LEFT JOIN @events b ON (a.uid = b.uid) 
INNER JOIN @evtypes c ON ( b.evtype = c.evtype)  
GROUP BY a.uname 
ORDER BY a.uname

The events table does not even contain a record for your 4th record and hence it's exclusion from the list. Take out the red line and your query will work.

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-26 : 09:51:04

Change the inner join to a left join and you're sorted.

-------
Moo.

skillile
Posting Yak Master

208 Posts

Posted - 2003-06-26 : 09:53:22

Yep,

I agree.

I am trying to join my lookup table eventtypes so I can get the headings. I can do a seperate select for these but, I was wondering if I could adjust anything to accomodate all three tables.

Thanks

slow down to move faster...

skillile
Posting Yak Master

208 Posts

Posted - 2003-06-26 : 09:55:52

sorry

thanks mr_mist

I must be slipping

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources