a join problem - 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

a join problem

Author

Topic

togy
Starting Member

5 Posts

Posted - 2009-03-22 : 17:08:29

i ve got 2 tables. first of them is called "journalist" and the other one is "news". there are 3 journalist in the first table and 7 records in the news table. all of the news are belong to the 3 journalists. i wanna make a query that makes a table like this;

journalist1 lastNewsIdOfHim lastNewsDateOfHim
journalist2 lastNewsIdOfHim lastNewsDateOfHim
journalist3 lastNewsIdOfHim lastNewsDateOfHim

NOTE: i dont wanna get all news records, i only wanna get the last news of the journalist. so my table will have just 3 rows.

thanks...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-22 : 17:35:32

If you posted the columns in your table, that would help. Your query would be SOMETHING like this:

select
   j.journalist_id,
   max(news_id) as lastNewsIdOfHim,
   max(news_date) as lastNewsDateOfHim
from
   journalist j
   inner join news n
      on j.journalist_id = n.journalist_id
group by
   j.journalist_id

Use left join instead of inner join if you want journalists who never wrote an article to be listed.

If the newsId is not something that monotonically increases, this may not (or may) give you what you are looking for.

Itamar
Starting Member

19 Posts

Posted - 2009-03-23 : 04:42:49

Togy,
You can do it the way sunitabeck showed you, but if you are new in this and going to work a lot with the database, you can try one of the 3rd party tools on the market that will help you with the grouping, or any other querys.
Query tool - [url]http://nobhillsoft.com/MarieAlix.aspx[/url]

Nob Hill Software - Tools for Database People
SQL Version Control, Database Compare, Query Tools and more. www.nobhillsoft.com

togy
Starting Member

5 Posts

Posted - 2009-03-23 : 19:05:15

sunitabeck thanks,
it works. but what should i write if i wanna get news_header and news_body datas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-23 : 19:10:06

quote:
Originally posted by togy

sunitabeck thanks,
it works. but what should i write if i wanna get news_header and news_body datas?

It depends on what version of SQL Server you are using. If you are using SQL Server 2000, then you'll need to use a derived table. If you are using SQL Server 2005, then you can use the ROW_NUMBER() function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

togy
Starting Member

5 Posts

Posted - 2009-03-24 : 09:52:42

tkizer,
can you show it in a little script. thanks.

togy
Starting Member

5 Posts

Posted - 2009-03-24 : 11:52:18

i am using sql server 2005. how can i use row_number() function?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources