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

Author

Topic

yumyum113
Starting Member

31 Posts

Posted - 2010-05-10 : 12:35:50

Hi all,

I've been trying to figure out a query but have not been sucessfull for the last two days. Hopefully somebody could help me, I tried looking for similar problem poster here but failed to find one. I appriciate any help in advance. Please see below

declare @topictbl table
(
topicid int,
updateid int,
topicname varchar(50),
topicdetails varchar(100)
)

INSERT INTO @topictbl
SELECT 1,2,'topic 1','topic 1 details' union all
SELECT 2,2,'topic 2','topic 2 details'

declare @topicagreement table
(
topicid int,
userid int,
understand bit
)

INSERT INTO @topicagreement
SELECT 1,1,1 union all
SELECT 2,2,0

--result that i want to get
topicid userid understand
1 1 1
2 1 null
1 2 null
2 2 0

--First query i've tried
select t.topicid, ta.userid, ta.understand
from @topictbl t
left join @topicagreement ta on t.topicid = ta.topicid

--2nd query i've tried
select t.topicid, ta.userid, ta.understand
from @topictbl t
full outer join @topicagreement ta on ta.topicid =t.topicid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 13:02:44

[code]SELECT m.topicid,m.userid,ta1.understand
FROM
(
SELECT t.topicid,ta.userid
FROM
(SELECT DISTINCT topicid FROM @topictbl ) t
CROSS JOIN (SELECT DISTINCT userid FROM @topicagreement ) ta
)m
LEFT JOIN @topicagreement ta1
ON ta1.topicid = m.topicid
AND ta1.userid = m.userid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-10 : 13:04:31

It's not very neat, but...

select t.topicid, ta.userid, 
		(SELECT understand FROM @topicagreement s
		WHERE s.topicid = t.topicid
			AND s.userid = ta.userid)
from @topictbl t
outer apply (SELECT userid
			FROM @topicagreement) ta

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-10 : 13:04:59

You did not describe the result you want is based on what. So I will guess that you want a row for every topic an user combination. But the users may come from a different table (users) or come from the same table (topicagreement). So can confirm that and decide from where users come.

yumyum113
Starting Member

31 Posts

Posted - 2010-05-10 : 13:21:02

Thank you all for taking time to reply. Both visak and dba's answer did give me the result that i'm looking for, i'll try to add more sample data and see if it still comes up with the expected result. Just wish there is a simplier way to do this. Thank you so much.

To answer malpashaa, basically topictbl table contains several topic and this topic belong to an update. The topicagreement table contain information if users, understand a topic from topictbl. One user may have read or not read the topic at one time. Thus when a user have not yet read a topic he/she will not be able to reply if he/she understood the topic, in this case he/she will not have a record in topicagreement for that particular topic. Hope i was able to explain it for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources