DISTINCT into SQL statement is not working very well

Mohamad Simo

10y883

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
Accepted 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..
+1
Mohamad Simo10y ago
Thank you so much finally it fixed :-)
+1
Mohamad Simo10y ago
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)
0
Manas Mohapatra10y ago
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.
+1
Mohamad Simo10y ago
CREATE TABLE [dbo].[UserInfo](
[UsrID] [int] IDENTITY(1,1) 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(1,1) 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,
+1
Manas Mohapatra10y ago
Please provide two table structure and provide an example what you want exactly in your result set.
0