DISTINCT into SQL statement is not working very well

Question

Hi

The below code is working fine with one problem that is shown many records where it suppose to show only one. (for example: if a user from UserInfo table has 10 ads into ads table where 5 of those ads has value similar to Query String result value, so he repeat the user 5 times where it suppose to appear once whatever he have ads where Wtag column.)

i make quick screen record explaining the issue hope that will be more clear to understand the issue, please have a look

https://www.youtube.com/watch?v=lh2GY2_whVM&feature=youtu.be

SELECT DISTINCT UI.[UID]
,UI.[Country]
,UI.[State]
,UI.[City]
,UI.[Logo]
,UI.[Website]
,UI.[UsrType]
,SUBSTRING(UI.[CompDesc], 1, 60) AS CompDesc
,UI.[BizCateg]
,UI.[BizSubCateg]
,UI.[Twitter]
,UI.[GooglePlus]
,UI.[Facebook]
,UI.[CompNme]
,UI.[RegDate]
,AD.[Wtags]
FROM UserInfo AS UI
JOIN ads AS AD ON AD.[UID] = UI.[UID]
WHERE UI.[Country] = @Location AND UI.[UsrType] = 'Business'
AND CHARINDEX(@Wtag, AD.[Wtags])> 0 ORDER BY UI.[RegDate] DESC

Manas Mohapatra · Answer

Try with below query: SELECT UI.[UID] UI.[Country] UI.[State] UI.[City] UI.[Logo] UI.[Website] UI.[UsrType] SUBSTRING(UI.[CompDesc] 1 60) AS CompDesc UI.[BizCateg] UI.[BizSubCateg] UI.[Twitter] UI.[GooglePlus] UI.[Facebook] UI.[CompNme] UI.[RegDate] FROM UserInfo AS UI JOIN (SELECT DISTINCT ads.[UID] FROM ads WHERE CHARINDEX(@Wtag ads.[Wtags])> 0) AS AD ON AD.[UID] = UI.[UID] WHERE UI.[Country] = @Location AND UI.[UsrType] = 'Business' ORDER BY UI.[RegDate] DESC Note: I believe you don't want wtag column so it is removed from query..

Mohamad Simo · Answer

Thank you so much finally it fixed :-)

Manas Mohapatra · Answer

As per query it will return multiple records per UserID because you are using join. Let's say user1 has 6 advs. Then it will return 6 records with duplicate with only different column is wtag . My question is what exactly do you want in wtag column. Because per user there will be different values in wtag column.

Mohamad Simo · Answer

CREATE TABLE [dbo].[UserInfo]( [UsrID] [int] IDENTITY(11) NOT NULL [UID] [nvarchar](max) NOT NULL [Email] [nvarchar](max) NULL [Country] [nvarchar](100) NULL [State] [nvarchar](100) NULL [City] [nvarchar](100) NULL [AdsID] [int] NULL [Logo] [nvarchar](max) NULL [Website] [nvarchar](max) NULL [RegDate] [date] NULL [UsrType] [varchar](20) NULL [BizCateg] [varchar](35) NULL [BizSubCateg] [varchar](35) NULL [CompNme] [nvarchar](max) NULL [CompDesc] [nvarchar](max) NULL [Facebook] [nvarchar](max) NULL [GooglePlus] [nvarchar](max) NULL [Twitter] [nvarchar](max) NULL [image1] [nvarchar](max) NULL [Wtag] [nvarchar](50) NULL [DispContInfo] [nvarchar](3) NULL [approv] [nvarchar](1) NULL ---------------------------------------------------- CREATE TABLE [dbo].[ads]( [AdsID] [int] IDENTITY(11) NOT NULL [UID] [nvarchar](max) NULL [Section] [nvarchar](50) NULL [Category] [nvarchar](50) NULL [Country] [nvarchar](100) NOT NULL [State] [nvarchar](100) NULL [City] [nvarchar](100) NULL [AdsTit] [nvarchar](max) NULL [AdsDesc] [nvarchar](max) NULL [AdsPrice] [int] NULL [Img1] [nvarchar](max) NULL [Wtags] [nvarchar](200) NULL [AdsDate] [date] NULL [Status] [nvarchar](20) NULL [Logo] [nvarchar](max) NULL [AdType] [nvarchar](7) NULL [approvAds] [nvarchar](1) NULL [QAdsEmail] [nvarchar](max) NULL [QAdsTeleNum] [nvarchar](100) NULL

Mohamad Simo · Answer

Wtags is inside ads table as it mention which is store all ads that has been added by users from UserInfo table so lets say Manas he is already user in my website so every time he want to add Ads he need to write keywords about his Ads lets say Manas has added 6 cars advs 3 of them he write into Wtag (which is keywords of the Ads) Toyota so when i write Toyota then Manas profile will appear three times where it suppose to appear once (when showing Manas info once that mean this user has one or more advs which has value Toyota so the user can click on your name and see all your ads)

Manas Mohapatra · Answer

Please provide two table structure and provide an example what you want exactly in your result set.