Select 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select query

Author

Topic

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-07-08 : 11:24:50

Hello it's me again running against the time. Well the scenario of my query is to do a threads view like this forum has, but a bit different of course, it's 3 tables one is the postsTable with fields post_id, thread_id, user_id, title, text and another is threads table which contain details of the threads with fields thread_id, rate and the last one that is called standBy with fields thread_id, user_id, which is for the users who want to be in touch with the thread.
So I have to get the first most recent post created for each thread and last one, and see if the current user are “in touch” with the thread. The query I think is almost working and it’s the following:

SELECT t.thread_id, p.post_id, lp.post_id, t.rate, t.type, t.views, t.posts, t.numberVotes, p.title, p.answer,
s.thread_id,
u.user_id As 'firstUser', u.userType_id as 'firstUserType', u.nickname as 'firstNickname',
lu.user_id AS 'lastUser', lu.userType_id AS 'lastUserType', lu.nickname AS 'lastNickname',
min(p.creation) as 'firstCreation', max(lp.creation) AS 'lastCreation'
FROM solarPosts AS p
INNER JOIN solarThreads AS t ON p.thread_id = t.thread_id
INNER JOIN solarUsers AS u ON p.user_id = u.user_id
INNER JOIN solarPosts AS lp ON lp.thread_id = t.thread_id
INNER JOIN solarUsers AS lu ON lp.user_id = lu.user_id
LEFT OUTER JOIN solarStandBy AS s ON t.thread_id = s.thread_id
Where s.user_id = @user_id
GROUP BY t.thread_id, p.post_id, lp.post_id, t.rate, t.type, t.views, t.posts, t.numberVotes, p.title, p.answer,
s.thread_id, u.user_id, u.userType_id, u.nickname, lu.user_id, lu.userType_id, lu.nickname
having p.post_id = min(p.post_id) and lp.post_id = max(lp.post_id)

The having clause is doing nothing, and the left outer join is working except when you put the Where clause because the nulls fields don’t appear in case that there is no “in touch” or “listener” for this user.

Thank you very much for your patient.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-08 : 11:34:06

LEFT OUTER JOIN solarStandBy AS s ON t.thread_id = s.thread_id
Where s.user_id = @user_id

LEFT OUTER JOIN solarStandBy AS s ON t.thread_id = s.thread_id and s.user_id = @user_id

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-08 : 11:34:37

I can't imagine what the HAVING clause is doing...

Peter Larsson
Helsingborg, Sweden

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-07-08 : 12:19:34

Thank you very much Peso for the quick reply, and my apology because I hadn't explained about the HAVING clause. Well I putted there because the query was selecting every post with some arrangement of solarPosts table, which I would like for each row had only the first post with the last, and I had saw that if I choose only those fields “having p.post_id = min(p.post_id) and lp.post_id = max(lp.post_id)” would work. The query gets out of the boundaries when I put any field from solarPosts except those that have function.

A picture showing what is happening

thread_id post_id post_id thread_id firstCreation lastCreation
----- ----------- ----------- ----------- ----------------------- -----------------------
9 27 27 9 2007-07-08 11:41:04.373 2007-07-08 11:41:04.373
10 28 28 10 2007-07-08 11:42:22.153 2007-07-08 11:42:22.153
11 29 29 11 2007-07-08 11:42:28.467 2007-07-08 11:42:28.467
12 30 30 NULL 2007-07-08 11:42:51.967 2007-07-08 11:42:51.967
12 30 32 NULL 2007-07-08 11:42:51.967 2007-07-08 11:47:09.717
12 30 33 NULL 2007-07-08 11:42:51.967 2007-07-08 11:47:37.500
12 30 34 NULL 2007-07-08 11:42:51.967 2007-07-08 15:00:39.577
13 31 31 13 2007-07-08 11:43:36.467 2007-07-08 11:43:36.467
12 32 30 NULL 2007-07-08 11:47:09.717 2007-07-08 11:42:51.967
12 32 32 NULL 2007-07-08 11:47:09.717 2007-07-08 11:47:09.717
12 32 33 NULL 2007-07-08 11:47:09.717 2007-07-08 11:47:37.500
12 32 34 NULL 2007-07-08 11:47:09.717 2007-07-08 15:00:39.577
12 33 30 NULL 2007-07-08 11:47:37.500 2007-07-08 11:42:51.967
12 33 32 NULL 2007-07-08 11:47:37.500 2007-07-08 11:47:09.717
12 33 33 NULL 2007-07-08 11:47:37.500 2007-07-08 11:47:37.500
12 33 34 NULL 2007-07-08 11:47:37.500 2007-07-08 15:00:39.577
12 34 30 NULL 2007-07-08 15:00:39.577 2007-07-08 11:42:51.967
12 34 32 NULL 2007-07-08 15:00:39.577 2007-07-08 11:47:09.717
12 34 33 NULL 2007-07-08 15:00:39.577 2007-07-08 11:47:37.500
12 34 34 NULL 2007-07-08 15:00:39.577 2007-07-08 15:00:39.577

(20 row(s) affected)

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-07-08 : 12:26:16

My apology also because of the text I'll fomart that later I'm running out of time. Thank you again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-08 : 12:49:22

The above is the sample data? Where is the expected output?

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2007-07-08 : 16:42:04

This may be what you are after, it's to trivial for Peter anyway :)

SELECT t.thread_id
	,fp.post_id
	,lp.post_id
	,t.rate
	,t.type
	,t.views
	,t.posts
	,t.numberVotes
	,fp.title
	,fp.answer
	,s.thread_id
	,fu.user_id As 'firstUser'
	,fu.userType_id as 'firstUserType'
	,fu.nickname as 'firstNickname'
	,lu.user_id AS 'lastUser'
	,lu.userType_id AS 'lastUserType'
	,lu.nickname AS 'lastNickname'
	,min(fp.creation) as 'firstCreation'
	,max(lp.creation) AS 'lastCreation'
FROM solarThreads AS t
INNER JOIN (
	SELECT thread_id, MIN(post_id) AS firstPostID, MAX(post_id) AS lastPostID
    FROM solarPosts
	GROUP thread_id
) DTbl
	ON DTbl.thread_id = t.thread_id
INNER JOIN solarPosts AS fp
	ON fp.post_id = DTbl.firstPostID
INNER JOIN solarPosts AS lp
	ON lp.post_id = DTbl.lastPostID
INNER JOIN solarUsers AS fu
	ON fu.user_id = fp.user_id 
INNER JOIN solarUsers AS lu
	ON lu.user_id = lp.user_id
LEFT OUTER JOIN solarStandBy AS s
	ON s.thread_id = t.thread_id AND s.user_id = @user_id

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-07-09 : 13:44:06

PSamsig, that was exactly what I wanted. Thank you very much. That's new for me: create a table on the fly inside of a inner join selection, very interesting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources