locked
Removing duplicate data from resultset RRS feed

  • Question

  • User-826336654 posted

    Hi All,

    I have 2 tables in my database. One for products and One ProductImages.

    Products table has the following columns

    ProductId, Name, Description And Price.

    ProductsImages table has the following columns

    ProductImageId, productId, ImageName, IsThumbNail

    What I want to achieve is to get all products with thumbnail.

    I write the following code.

    SELECT	 p.[ProductId]		AS 'ProductId'
    	,p.[ProductName]	AS 'Name'
    	,p.[ActualPrice]	AS 'Price'
    	,pimg.[Name]		AS 'ImageName'
    FROM	[dbo].[Products] p
    LEFT JOIN	[dbo].[ProductImages] pimg ON pimg.ProductId = p.ProductId

    The result is show below.

    ProductId	Name	Price	ImageName
    10001	Nikon Coolpix L340 Bridge Camera - Black	114.85	Nikon1001a
    10001	Nikon Coolpix L340 Bridge Camera - Black	114.85	Nikon1001b
    10001	Nikon Coolpix L340 Bridge Camera - Black	114.85	Nikon1001c
    10001	Nikon Coolpix L340 Bridge Camera - Black	114.85	Nikon1001d
    10001	Nikon Coolpix L340 Bridge Camera - Black	114.85	Nikon1001e
    10002	Sony DSCW800 Digital Compact Camera	59.99	SonyDSCW80001a
    10002	Sony DSCW800 Digital Compact Camera	59.99	SonyDSCW80001b
    10002	Sony DSCW800 Digital Compact Camera	59.99	SonyDSCW80001c
    10002	Sony DSCW800 Digital Compact Camera	59.99	SonyDSCW80001d
    10002	Sony DSCW800 Digital Compact Camera	59.99	SonyDSCW80001e
    10003	GoPro HERO+ Camera with LCD Touch Screen	194.00	NULL
    10004	Ricoh Theta S 360 Degree Digital Camera	299.00	NULL
    10005	Fujifilm Instax Mini 8 Instant Camera Gift Bundle with 40 Shots	83.26	NULL
    10006	Polaroid Snap Instant Digital Camera (White) wih ZINK Zero Ink Printing Technology	89.99	NULL

    What I want is that productId 10001 and 10002 should only have that row which has IsThumbNail true but unfortnately it is showing all records. 

    Any suggestion to improve this query.

    Thanks in advance.

    Wednesday, March 23, 2016 9:29 AM

Answers

  • User-826336654 posted

    After spending 2 days, I come up with the correct solution.

    SELECT	 p.[ProductId]		AS 'ProductId'
    	,p.[ProductName]	AS 'Name'
    	,p.[ActualPrice]	AS 'Price'
    	,pimg.[Name]		AS 'ImageName'
    FROM	  [dbo].[Products] p
    LEFT JOIN [dbo].[ProductImages] pimg ON pimg.ProductId = p.ProductId
    WHERE pimg.[IsThumbNail] = 1 OR pimg.[IsThumbNail] IS NULL

    Result is shown below

    ProductId	Name	                                                                            Price       ImageName
    10001	        Nikon Coolpix L340 Bridge Camera - Black	                                    114.85	Nikon1001a.jpg
    10002	        Sony DSCW800 Digital Compact Camera	                                            59.99	SonyDSCW80001a.jpg
    10003	        GoPro HERO+ Camera with LCD Touch Screen	                                    194.00	NULL
    10004	        Ricoh Theta S 360 Degree Digital Camera	                                            299.00	NULL
    10005	        Fujifilm Instax Mini 8 Instant Camera Gift Bundle with 40 Shots	                    83.26	NULL
    10006	        Polaroid Snap Instant Digital Camera (White) wih ZINK Zero Ink Printing Technology  89.99	NULL

    Thanks for all replies.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2016 6:07 PM
  • User77042963 posted
    SELECT	 p.[ProductId]		AS 'ProductId'
    	,p.[ProductName]	AS 'Name'
    	,p.[ActualPrice]	AS 'Price'
    	,pimg.[Name]		AS 'ImageName'
    FROM	  [dbo].[Products] p
    LEFT JOIN [dbo].[ProductImages] pimg ON pimg.ProductId = p.ProductId
    and (pimg.[IsThumbNail] = 1  Or pimg.[IsThumbNail] IS NULL)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2016 7:37 PM

All replies

  • User941753370 posted

    Hi,

    Try:

    SELECT	 p.[ProductId]		AS 'ProductId'
    	,p.[ProductName]	AS 'Name'
    	,p.[ActualPrice]	AS 'Price'
    	,pimg.[Name]		AS 'ImageName'
    FROM	[dbo].[Products] p
    INNER JOIN	[dbo].[ProductImages] pimg ON pimg.ProductId = p.ProductId
    WHERE pimg.IsThumbNail = 'true'

    Hope this help

    Wednesday, March 23, 2016 1:18 PM
  • User269881539 posted

    One way would be :

    SELECT	 p.[ProductId]		AS 'ProductId'
    	,p.[ProductName]	AS 'Name'
    	,p.[ActualPrice]	AS 'Price'
    	,(SELECT COUNT(*) FROM [dbo].[ProductImages] pimg WHERE pimg.ProductId = p.ProductId) AS Thumbnails
    FROM	[dbo].[Products] p

    Or :

    SELECT	 p.[ProductId]		AS 'ProductId'
    	,p.[ProductName]	AS 'Name'
    	,p.[ActualPrice]	AS 'Price'
    	,CASE WHEN (SELECT COUNT(*) FROM [dbo].[ProductImages] pimg WHERE pimg.ProductId = p.ProductId) > 0 THEN 1 ELSE 0 END AS Thumbnail
    FROM	[dbo].[Products] p

    Thursday, March 24, 2016 3:51 PM
  • User-826336654 posted

    After spending 2 days, I come up with the correct solution.

    SELECT	 p.[ProductId]		AS 'ProductId'
    	,p.[ProductName]	AS 'Name'
    	,p.[ActualPrice]	AS 'Price'
    	,pimg.[Name]		AS 'ImageName'
    FROM	  [dbo].[Products] p
    LEFT JOIN [dbo].[ProductImages] pimg ON pimg.ProductId = p.ProductId
    WHERE pimg.[IsThumbNail] = 1 OR pimg.[IsThumbNail] IS NULL

    Result is shown below

    ProductId	Name	                                                                            Price       ImageName
    10001	        Nikon Coolpix L340 Bridge Camera - Black	                                    114.85	Nikon1001a.jpg
    10002	        Sony DSCW800 Digital Compact Camera	                                            59.99	SonyDSCW80001a.jpg
    10003	        GoPro HERO+ Camera with LCD Touch Screen	                                    194.00	NULL
    10004	        Ricoh Theta S 360 Degree Digital Camera	                                            299.00	NULL
    10005	        Fujifilm Instax Mini 8 Instant Camera Gift Bundle with 40 Shots	                    83.26	NULL
    10006	        Polaroid Snap Instant Digital Camera (White) wih ZINK Zero Ink Printing Technology  89.99	NULL

    Thanks for all replies.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2016 6:07 PM
  • User77042963 posted
    SELECT	 p.[ProductId]		AS 'ProductId'
    	,p.[ProductName]	AS 'Name'
    	,p.[ActualPrice]	AS 'Price'
    	,pimg.[Name]		AS 'ImageName'
    FROM	  [dbo].[Products] p
    LEFT JOIN [dbo].[ProductImages] pimg ON pimg.ProductId = p.ProductId
    and (pimg.[IsThumbNail] = 1  Or pimg.[IsThumbNail] IS NULL)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2016 7:37 PM