January 23, 2013 at 6:30 am
I have the following tables
Product --stored for productid
ProductRelation -- storing linked product id's
DECLARE @product table(ProductID int)
DECLARE @ProductRelation TABLE (FirstProductID int,SecondProductID int)
INSERT INTO @product
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
--SELECT * FROM @product
INSERT INTO @ProductRelation
SELECT 1,2
UNION ALL
SELECT 3,5
UNION ALL
SELECT 2,6
UNION ALL
SELECT 1,4
UNION ALL
SELECT 1,4
--SELECT * FROM @ProductRelation
SELECT ProductID,'Not Linked' AS 'Relation' FROM @product
UNION
SELECT FirstProductID,'Linked' from @ProductRelation
UNION
SELECT SecondProductID ,'Linked' FROM @ProductRelation
Above query results repeating ProductID
I wanted to select distinct ProductID...if there is relation between product id then it should display the ProductID with 'Linked'
If no relation then ProductID with 'Not Linked'
I want the expected result like this
ProductID Relation
1Linked
2Linked
3Linked
4Linked
5Linked
6Linked
7Not Linked
8Not Linked
9Not Linked
10Not Linked
January 23, 2013 at 6:50 am
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
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
January 23, 2013 at 6:57 am
thanks chris for the reply
can you tell me the use of SELECT TOP 1 n=1 ....i never use this
January 23, 2013 at 10:07 am
try this one...........
DECLARE @product table(ProductID int)
DECLARE @ProductRelation TABLE (FirstProductID int,SecondProductID int)
INSERT INTO @product
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
--SELECT * FROM @product
INSERT INTO @ProductRelation
SELECT 1,2
UNION ALL
SELECT 3,5
UNION ALL
SELECT 2,6
UNION ALL
SELECT 1,4
UNION ALL
SELECT 1,4
--SELECT * FROM @ProductRelation
SELECT tmp.ProductID, min(tmp.Relation) FROM
(SELECT ProductID,'Not Linked' AS 'Relation' FROM @product
UNION
SELECT FirstProductID,'Linked' from @ProductRelation
UNION
SELECT SecondProductID ,'Linked' FROM @ProductRelation) tmp
GROUP BY ProductID
January 24, 2013 at 1:04 am
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
January 24, 2013 at 8:39 am
I think EXISTS would be the clearest indication that you just want to know if a matching row exists, not retrieve data from that row. For example:
SELECT
p.ProductID, CASE WHEN EXISTS(SELECT 1 FROM @ProductRelation pr
WHERE p.ProductID IN (pr.FirstProductID, pr.SecondProductID))
THEN 'Linked' ELSE 'Not Linked' END
FROM @product p
ORDER BY
p.ProductID
I suspect this also might perform better on large table(s), but I prefer it just as much for the clarity of showing what's actually being looked for.
Edit: Reformatted the EXISTS() to fit better in the code window.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 24, 2013 at 9:43 pm
SELECT p.ProductID,
CASE WHEN R1.FirstProductID IS NULL and R2.SecondProductID IS NULL then 'Not Linked' ELSE 'Linked' END Relation
FROM @product p
LEFT JOIN @ProductRelation R1 ON R1.FirstProductID = p.ProductID
LEFT JOIN @ProductRelation R2 ON R2.SecondProductID= p.ProductID
ORDER BY p.ProductID
OR
SELECT p.ProductID,
CASE WHEN R.FirstProductID IS NULL and R.SecondProductID IS NULL then 'Not Linked' ELSE 'Linked' END Relation
FROM @ProductRelation R
RIGHT JOIN @product p ON p.ProductID = R.FirstProductID OR p.ProductID = R.SecondProductID
ORDER BY p.ProductID
_____________
Code for TallyGenerator
January 25, 2013 at 1:11 am
Except for Sergiy's first query, the plans are very similar and are costed exactly equal. I'd guess that my APPLY version with two extra operators (Compute scalar and TOP, each costed at 0%) would perform slightly slower than Sergiy's second query. TOP doesn't come for free even if the plan says otherwise. Scott's might also be a little slower with a left semi join NL rather than a left outer join NL.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply