CODEWITHSUNDEEP
sqlsql-server-2008distinct
SQL006
SQL006

Reputation: 492

Get distinct result

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

Upvotes: -1

Views: 81

Answers (3)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

SELECT
   P.ProductID,
   CASE WHEN COUNT(R.FirstProductID) > 0
        THEN 'Linked'
        ELSE 'Not Linked'
   END Relation
FROM Product P
   LEFT JOIN ProductRelation R
      ON P.ProductID = R.FirstProductID
        OR P.ProductID = R.SecondProductID
GROUP BY P.ProductID

Upvotes: 1

Frederic
Frederic

Reputation: 1028

well... I love this opportunity to use some unpivot tricks!

select P.ProductID
,isnull(L.relation,'Not Linked') as relation
from @Product P
left outer join (select U.ProductID, cast('Linked' as varchar(max)) as relation
                from @ProductRelation
                unpivot (ProductID for prod in (FirstProductID,SecondProductID))U
                group by U.ProductID
)L
on L.ProductID = P.ProductID

Upvotes: 1

Luuk
Luuk

Reputation: 1969

You can use a left join combined with a CASE WHEN in SQL Server

SELECT DISTINCT 
   ProductID,
   CASE WHEN @ProductRelation.FirstProductId IS NULL THEN 'Not Linked' ELSE 'Linked' END [Status]
FROM @Product 
LEFT JOIN @ProductRelation ON @Product.ProductID = @ProductRelation.FirstProductId
RIGHT JOIN @ProductRelation ON @Product.ProductID = @ProductRelation.SecondProductId

Upvotes: 0

Related Questions