Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get distinct value Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 6:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:50 AM
Points: 182, Visits: 998
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

1 Linked
2 Linked
3 Linked
4 Linked
5 Linked
6 Linked
7 Not Linked
8 Not Linked
9 Not Linked
10 Not Linked



Post #1410531
Posted Wednesday, January 23, 2013 6:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1410538
Posted Wednesday, January 23, 2013 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:50 AM
Points: 182, Visits: 998
thanks chris for the reply

can you tell me the use of SELECT TOP 1 n=1 ....i never use this
Post #1410546
Posted Wednesday, January 23, 2013 10:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 2, 2014 10:20 AM
Points: 19, Visits: 121
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
Post #1410696
Posted Thursday, January 24, 2013 1:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
SQL006 (1/23/2013)
thanks chris for the reply

can 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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1410966
Posted Thursday, January 24, 2013 8:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:33 PM
Points: 2,208, Visits: 3,323
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1411174
Posted Thursday, January 24, 2013 9:43 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:25 PM
Points: 4,573, Visits: 8,354
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


Post #1411458
Posted Friday, January 25, 2013 1:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1411510
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse