SQL006 (1/23/2013)
thanks chris for the replycan you tell me the use of SELECT TOP 1 n=1 ....i never use this
SELECT
p.ProductID,
Relation = CASE x.n WHEN 1 THEN 'Linked' ELSE 'Not Linked' END
FROM @product p
OUTER APPLY (
SELECT TOP 1 n=1
FROM @ProductRelation r
WHERE p.ProductID IN (r.FirstProductID, r.SecondProductID)
) x
ORDER BY p.ProductID
Sure - n is the column name, 1 is the value assigned to it. I'm not interested in any table values from the OUTER APPLY, only whether or not a row exists, and this makes it clear.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden