THANK YOU!
That is just what I wanted, it worked great.
Oh, sorry about my poor English. You understod me correctly.

Many thanks!
JW


> Your specification seems flawed.

What do you mean by the phrase: "...the most recent published articles by
the users"?  Your use of the plural "articles" there is curious.  Do you
really mean you want the most recently published *article* (singular) for
each user, or do you want a list of them?  If a list of them, then what is
the meaning of "most recent"?

I assume your tables look something like:

CREATE TABLE Users (
	UserID integer NOT NULL PRIMARY KEY,
	UserName varchar(xx) NOT NULL,
	...

CREATE TABLE Articles (
	ArticleID integer NOT NULL PRIMARY KEY,
	UserID integer NOT NULL REFERENCES Users (UserID),
	CreateDate datetime NOT NULL,
	...

This would imply that a User may have many articles each with their
associated CreateDate, and I'm guessing you really want the most recent
article published by each user.

You almost gave yourself the answer.  You observed correctly that your 
query
...returns all articles ever written.".  So now we just have to extract
those rows from your query where the CreateDate is a maximum for each user.
Doing that would be easy if we had a table that listed the UserID and the
maximum CreateDate for each user.  Then we can just JOIN the two tables, 
and
that would extract the data you're looking for.  A query which would
construct that table would be:

	SELECT UserID, Max(CreateDate) AS CreateDate FROM Articles GROUP 
BY UserID;

We give that (sub)query a name (q2), and pretend its a real table and JOIN
it to your original query (q1), as:

SELECT q1.UserID, q1.UserName, q1.ArticleID, q1.CreateDate
	FROM (SELECT us.UserID, us.UserName, ar.ArticleID, ar.CreateDate
		FROM Users us INNER JOIN Articles ar ON 
us.UserID=ar.UserID) as q1
	INNER JOIN (SELECT UserID, Max(CreateDate) AS CreateDate FROM 
Articles
GROUP BY UserID) as q2
		ON q1.UserID=q2.UserID AND q1.CreateDate=q2.CreateDate)
WHERE q1.Activity=1;

Doing this via a correlated subquery is left as an exercise to the reader
;-)
--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: JW [mailto:robot@k...]
Sent: Friday, September 06, 2002 8:52 PM
To: sql language
Subject: [sql_language] Joing two tables


I really need some guidance in the following matter.
I have two tables, Users and Articles. I want a list of all the members
(UserID and UserName) and the ArticleID of the most recent published
articles by the users.
For example;
UserID     UserName     ArticleID     CreateDate
------------------------------------------------
1          Laura        12            2002-09-06
2          Ben          6             2002-09-04
3          Brett        16            2002-09-06


Running the query below returns all articles ever written.

SELECT US.UserID, US.UserName, AR.ArticleID, AR.CreateDate
FROM Users AS US
JOIN Articles AS AR
ON US.UserID=AR.UserID
WHERE US.Activity=1

All help is much appreciated!
JW

---
Change your mail options at http://p2p.wrox.com/manager.asp or
to unsubscribe send a blank email to