SELECT TOP 100 *
FROM
(
	SELECT Ud.UserID, UD.NameOnline, EventDate = F.dateAdded, TypeId = 1, 
	friendID as friend_counterID, null as photoID, UD2.nameOnline as friend_NameOnline
	from tblFriends F
	JOIN tblUserDetails UD ON F.userID = UD.UserID
	JOIN tblUserDetails UD2 ON F.FriendID = UD2.UserID

		WHERE UD.userID IN (SELECT userID FROM tblFriends WHERE friendID=1)

UNION ALL
	SELECT Ud.UserID, UD.NameOnline, EventDate = P.photoDate , TypeId = 3, counterID, photoID, null
	from tblExtraPhotos P
	JOIN tblUserDetails UD ON P.userID = UD.UserID

		WHERE UD.userID IN (SELECT userID FROM tblFriends WHERE friendID=1)
UNION ALL
	SELECT userID, nameOnline, EventDate = lastUpdated, TypeId = 4, null, null, null
	from tblUserDetails UD

		WHERE UD.userID IN (SELECT userID FROM tblFriends WHERE friendID=1)

UNION ALL
	SELECT userID, nameOnline, EventDate = date, TypeId = 5, null, null, null
	from tblUserDetails UD

		WHERE UD.userID IN (SELECT userID FROM tblFriends WHERE friendID=1)
) as data

ORDER BY eventDate DESC

quote:

---

Originally posted by mike123

Hi,

I have the following query below, and I have one concern with the way it was written. The

"WHERE UD.userID IN (SELECT userID FROM tblFriends WHERE friendID=1)"

part of the query appears 4 times. Would it be possible/more efficient to populate this into a temp table / variable and have it only execute once?

...

---

WHERE EXISTS (SELECT * FROM tblFriends F WHERE F.friendID = 1 AND F.userID = UD.userID)

SELECT UD.userID
    ,UD.nameOnline
    ,EventDate =
        CASE D.TypeId
            WHEN 4 THEN UD.lastUpdated
            ELSE UD.[date]
        END
    ,D.TypeId
    ,NULL, NULL, NULL
FROM tblUserDetails UD
    CROSS JOIN (
            SELECT 4 UNION ALL
            SELECT 5
        ) D (TypeId)
WHERE EXISTS (
        SELECT *
        FROM tblFriends F
         WHERE F.friendID = 1
            AND F.userID = UD.userID
    )