Help with query - 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

Help with query

Author

Topic

pjoe
Starting Member

3 Posts

Posted - 2007-07-05 : 21:54:25

Hi guys, im new to SQL and ive came across a question that been bothering me for a while now. The question was asking for a query that resulting a table from two tables. I dont have any idea how to solve it.

user table (int user_id,char name)
sample data(1,Guest)
sample data(2,Tester)

tagged table (int user_id,char day)
sample data(1,Monday)
sample data(2,Monday)
sample data(1,Tuesday)
sample data(2,Tuesday)
sample data(2,Thursday)
sample data(1,Friday)

result table (user_name, Mon, Tue, Wed, Thu, Fri)
sample data(Guest,Y,Y,N,N,Y)
sample data(Tester,Y,Y,N,Y,N)

Any help will be appreciated. Just point me to the right direction.
Thanks

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-07-06 : 00:19:05

is it?

declare @user table(userid int, [name] varchar(20))
insert @user
select 1, 'Guest' union all
select 2, 'Tester'

declare @tagged table(userid int, [day] varchar(20))
insert @tagged
select 1, 'Monday' union all
select 2, 'Monday' union all
select 1, 'Tuesday' union all
select 2, 'Tuesday' union all
select 2, 'Thursday' union all
select 1, 'Friday'

select distinct c.[name],
Mon = (case (select count(*) from @tagged x where [day] = 'Monday' AND x.userid = c.userid) when 0 then 'N' else 'Y' end),
Tue = (case (select count(*) from @tagged x where [day] = 'Tuesday' AND x.userid = c.userid) when 0 then 'N' else 'Y' end),
Wed = (case (select count(*) from @tagged x where [day] = 'Wednesday' AND x.userid = c.userid) when 0 then 'N' else 'Y' end),
Thu = (case (select count(*) from @tagged x where [day] = 'Thursday' AND x.userid = c.userid) when 0 then 'N' else 'Y' end),
Fri = (case (select count(*) from @tagged x where [day] = 'Friday' AND x.userid = c.userid) when 0 then 'N' else 'Y' end)
from @user c

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

pjoe
Starting Member

3 Posts

Posted - 2007-07-06 : 00:37:20

cwtriguns2002, thanks but your query too complicated for me. ill try to understand it. BTW ive managed to get this result

result table (name, Mon, Tue, Wed, Thu, Fri)
result data(Guest,1,0,0,0,0)
result data(Tester,1,0,0,0,0)
result data(Guest,0,1,0,0,0)
result data(Tester,0,1,0,0,0)
result data(Tester,0,0,0,1,0)
result data(Guest,0,0,0,0,1)

using this query

SELECT user.name,(tagged.day = ('Monday')) AS Mon,(tagged.day = ('Tuesday')) AS Tue,(tagged.day = ('Wednesday')) AS Wed,(tagged.day = ('Thursday')) AS Thu,(tagged.day = ('Friday')) AS Fri
FROM user,tagged
WHERE tagged.user_id = user.id

Questions now is how to convert 1 to Y, 0 to N and group the result by name.

Thanks

pjoe
Starting Member

3 Posts

Posted - 2007-07-06 : 00:54:32

lol.nvm.. ive manage to understand cwtriguns2002 solution. Never though it could be that easy. thanks again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-06 : 02:23:19

This will be faster as it does not use sub-query

DECLARE @user TABLE(userid int, [name] varchar(20))
INSERT @user
SELECT 1, 'Guest' 	UNION ALL
SELECT 2, 'Tester'

DECLARE @tagged TABLE(userid int, [DAY] varchar(20))
INSERT @tagged
SELECT 1, 'Monday' 	UNION ALL
SELECT 2, 'Monday' 	UNION ALL
SELECT 1, 'Tuesday' 	UNION ALL
SELECT 2, 'Tuesday' 	UNION ALL
SELECT 2, 'Thursday'	UNION ALL
SELECT 1, 'Friday'

SELECT	u.[name],
	[Mon]	= MAX(CASE WHEN t.[DAY] = 'Monday' 	THEN 'Y' ELSE 'N' END),
	[Tue]	= MAX(CASE WHEN t.[DAY] = 'Tuesday' 	THEN 'Y' ELSE 'N' END),
	[Wed]	= MAX(CASE WHEN t.[DAY] = 'Wednesday' 	THEN 'Y' ELSE 'N' END),
	[Thu]	= MAX(CASE WHEN t.[DAY] = 'Thursday' 	THEN 'Y' ELSE 'N' END),
	[Fri]	= MAX(CASE WHEN t.[DAY] = 'Friday' 	THEN 'Y' ELSE 'N' END)
FROM	@user u INNER JOIN @tagged t
	ON	u.userid	= t.userid
GROUP BY u.[name]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources