Build a query that return one value of each value

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)

Build a query that return one value of each value

Author

Topic

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-04-05 : 09:17:06

I'm trying to build a query that gets only one field of each value. Per example in a forum a table that records the posts and another table which record the title of the thread. In this case a user can have one or more posts in one thread, which is recorded in "posts" table. But if build a page that show all the posts made by this user, it wouldn't show every post, it would show only the title of each thread. So per example: if he gets more than one post in one thread it would show just one thread. How should I do a query for just get one post, of each thread made by this user?

These are the table to illustrate just the subject.

Threads

thread_id
title

Posts

thread_id
user_id
post

I was thinking something like a top(1) threads t left join posts p on t.thread_id = p.thread_id.

Thank you very much indeed.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-05 : 09:25:17

One post? on what basis? You will also need Posting date and time field to show just recently added post for each thread.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-04-05 : 09:29:17

yes, these table have datetime column, I didn't added because I'm just focusing in get just one post of each thread.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-04-05 : 09:33:02

Well As Harsh pointed out there should be DateTime field which will specify the threads recent behavoiur..

And i Dont see any primary key in your Posts Table...

Also if there would be an identity feild then, you would be able to retrive the most recent records... based on the identity value..

Chirag

http://chirikworld.blogspot.com/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-04-05 : 09:36:35

If you have date then this can be the query..


Select T.Title,P.User_ID,P.Post 
From Threads t Left outer Join Posts p On 
t.Thread_ID= p.Thread_ID
Where 
p.DateTime = 
(
	Select Max(p.DateTime) From Posts p1 Where p1.Thread_ID= p.Thread_ID and p.User_ID = p1.User_ID 
)

Chirag

http://chirikworld.blogspot.com/

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-04-05 : 09:46:16

Most recent post made by the requested user of each thread, would be perfect.

threads table:

thread_id < Primary key
user_id
forum_id
last_user_id
title
creation
last_answer
posts
vizuais
fixed

posts table:

post_id < Primary key
thread_id
user_id
title
texto
creation
voto

The best that I got:

Select t.* from threads t left outer join posts p
on t.thread_id = p.thread_id
where p.user_id = @user_id order by t.thread_id asc

Thank you very much for reply, chiragkhabaria and harsh_athalye, people from SQLTeam are the best. =D

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-04-05 : 09:52:59

I think that is the case, I will try here and see what I got.

Thank you very much chiragkhabaria.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-05 : 09:53:53

But this wouldn't give you just a single most recent post per thread.
Instead Chirag's solution will give be perfect solution for that.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-04-05 : 10:38:02

seems to be worked, but p1.Thread_ID= p.Thread_ID is tieing my brain, for me max would return just one value, looks like the instance:

Select T.Title,P.User_ID,P.Post
From Threads t Left outer Join Posts p On
t.Thread_ID= p.Thread_ID

is executed before that “Select Max(p.DateTime) From Posts p1 Where p1.Thread_ID= p.Thread_ID and p.User_ID = p1.User_ID”

is that right?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-05 : 10:46:17

In Chirag's solution, for each thread in outer Posts (p) table, maximum DateTime is determined from inner Posts (p1) table and it is passed to outer query, which gives you desired output. This type of query is called Correlated subquery.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-04-05 : 11:00:20

I got harsh_athalye it is a bit confused I think, it is the first time that I see this kind of query, it's remind me kind of I use "for" statement hard coding. How should I look for this kind of queries in Google, which have certain complexity?

Thank you very much.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-05 : 11:03:17

Google for "correlated subquery". You well get plenty of links to help you clarify your concepts.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-04-05 : 11:06:19

Well, here a bit explaination on the Query..

You wanted the result which are lastest for any post of that particular user...

I beliave the Select part is simple and you would have got it.. if not then do let us know..

now comming to the Where Clause, See if you the see the query its like the self join.. where the records is pulled out on the basis of the date.
so its means that get the records with the date which is equal to the maximum date for the same user... so it will nest to all the users and will get the records with the max date... which in your case will be most recent date...

The nesting is done in the sub queries "p1.Thread_ID= p.Thread_ID and p.User_ID = p1.User_ID"
Where the records are nested between the main query and sub query on the basis of the date and pulling the most recent records..

I hope this makes sense with you..

Chirag

http://chirikworld.blogspot.com/

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-04-05 : 11:43:30

Thank you very much chiragkhabaria for trying, but I think it would be easier to me understand if I see it debugging. Anyway, I figure that out thinking that query is executed per each record set, if that is the way so I understand it, else I try to live with that for while.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources