JOIN with ORDER BY clause?

like so often my Forums database design (in its simplest form) is:
Forums
-ForumID
-Title
-Category
ForumsMsgs
-fmID
-DateIn
-AuthorID
-Message

I need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?

SELECT f.*,fm.*

FROM Forums f

JOIN ForumsMsgs fm ON (f.ForumID=fm.fmID)

WHERE fm.DateIn IN (SELECT MIN(DateIn) FROM ForumsMsgs fm2 WHERE fm2.AuthorID=fm.AuthorID UNION SELECT MAX(DateIn) FROM ForumsMsgs fm2 WHERE fm2.AuthorID=fm.AuthorID)

Should work for you, although there may be multiple records returned if the author was able to create multiple messages with the same DateIn, and I'm not sure how you wanted to handle the case in which the author has only left a single message (Making both the first and last message the same one). There are better ways of doing the same thing, but I don't see a unique/primary key on the ForumsMsgs table (MsgID?)

|||

The following query is based on these assumptions for your two tables. ForumsMsgs.fmID is the Foriegn Key field for Forums.ForumID. I don't see any really good indication of what makes the record unique in ForumsMsgs, but you need a single value Primary Key field in ForumMsgs for this process to work. Let's call it msgID which could be a INT Identity(1,1) field.

The concept is to retreive a single PK record from ForumMsgs for each Forums.ForumID entry that matches your Order by criteria. Since you want both the newest and oldest messages, this will be done in two steps with a UNION operator.

The inner query (a) returns two records for each ForumID one each oldest/newest. This result is used to join back into the source tables to get your results from Forums (f) and ForumsMsgs (m). A SELECT DISTINCT is used so that you don't return two records that are identical, which will happen if a forum has only single message, makeing it both the oldest and newest.

SELECT DISTINCT
f.ForumID
, f.Title
, f.Category
, m.msgID
, m.DateIn
, m.AuthorID
FROM
( -- Inner Query to get the Oldest/Newest Message IDs
-- Gets the forum oldest message ID
SELECT
ForumID
, ( -- New Field that represents the msgID (Primary Key entry)
-- that matches the ForumID record
SELECT TOP 1
msgID
FROM ForumsMsgs
WHERE
ForumID = fmID
ORDER BY
fmID
, DateIn ASC
) AS msgID
FROM Forums
UNION
-- Gets the forum newest message ID
SELECT
ForumID
, ( -- New Field that represents the msgID (Primary Key entry)
-- that matches the ForumID record
SELECT TOP 1
msgID
FROM ForumsMsgs
WHERE
ForumID = fmID
ORDER BY
fmID
, DateIn DESC
) AS msgID
FROM Forums
) a
JOIN Forums f
ON a.ForumID = f.ForumID
JOIN ForumsMsgs m
ON a.msgID = m.msgID

Not knowing the size of your content, this will most likely run fine. However if very large applications, you may want to run the inner query (a) into a temporary table, then use the temporary table to join back into (f) and (m).

If you have the SQL Server AdventureWorks database installed, you can see the same effect with the following query, which returns the highest order quantity item from the Details table for each Header record. The where clause at the is used to limit the number of records returned for this example (CustomerID between 11000 and 13000).

SELECT
h.SalesOrderID
, h.CustomerID
, (
SELECT TOP 1
d.SalesOrderID
, d.SalesOrderDetailID
FROM sales.SalesOrderDetail d
WHERE
h.SalesOrderID = d.SalesOrderID
ORDER BY
d.SalesOrderID
, d.OrderQty desc
) a
FROM Sales.SalesOrderHeader h
JOIN sales.SalesOrderDetail d
ON a.SalesOrderDetailID = d.SalesOrderDetailID
where
CustomerID between 11000 and 13000

Hope this helps. Hopefully I did not make a syntax error, not having your tables I could not verify that.

Mike