locked
DISTINCT into SQL statement is not working very well RRS feed

  • 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]
    ,UI.[BizCateg]
    ,UI.[BizSubCateg]
    ,UI.[Twitter]
    ,UI.[GooglePlus]
    ,UI.[Facebook]
    ,UI.[CompNme]
    ,UI.[RegDate] 
    ,SUBSTRING([CompDesc], 1, 40) AS CompDesc  
    ,AD.[Wtags] 
    FROM UserInfo AS UI JOIN ads AS AD ON UI.UID = AD.UID WHERE UI.[Country] = @Location AND UI.[UsrType] = 'Business' 
    AND CHARINDEX(@Wtag, AD.[Wtags])> 0 ORDER BY UI.[RegDate] DESC

    Friday, April 22, 2016 9:56 PM

Answers

  • Thanks all it has been fixed as below finally

    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]
    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

    • Marked as answer by msimo1 Saturday, April 23, 2016 7:17 PM
    Saturday, April 23, 2016 7:17 PM

All replies

  • You're including the AD.[WTags] column in your result set - most likely this column contains different values for different rows.

    Also, you're not specifying which table the CompDesc column comes from - you should.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, April 22, 2016 10:27 PM
  • Wtags it suppose to be into statement without it the statement is nothing as the code will get user info from UserInfo table depending on Wtags (which is the query string) from ads table. In other way the statement will go through ads table inside Wtags and check the values which matching the query string value and if it found then it will take the UID and then show user info)
    Friday, April 22, 2016 10:46 PM
  • So, what exactly is your problem? Do you want to bring wTags into your query? If yes, then you'll get all the rows. If you only want the user who has the tags matching, then you don't include that column into your column list and then DISTINCT will work for you.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, April 22, 2016 11:27 PM
  • It is not clear to me what you want to achieve, but it is a common misconception that DISTINCT would only apply to one column. DISTINCT applies to the full list of columns. Thus, it one user has 10 ads matching @Wtag and there 6 different tags used for these ads, you would see six rows.

    If where is more than one matching tag, which one do you want to display?

    Saturday, April 23, 2016 8:51 AM
  • I dont know if you check the video or not but i think you probably get what the problem is, so there is no way to show one record using Wtags !
    Saturday, April 23, 2016 9:10 AM
  • I dont know if you check the video or not but i think you probably get what the problem is, so there is no way to show one record using Wtags !

    I did watch the video, but it did not make me any wiser. It's difficult to map your query what is displayed on that web page. A further handicap is that I don't know Arabic.

    Generally, for a problem like this, a good approach is to post:

    1) CREATE TABLE statements for your tables. (Possibly simplified to demonstrate the problem).
    2) INSERT statements with sample data, enough to highlight all aspects of the problem.
    3) The desired output given the sample.
    4) A short description of the business rules that explains why you want that result.
    5) Which version of SQL Server you are using.

    Saturday, April 23, 2016 12:05 PM
  • Thanks all it has been fixed as below finally

    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]
    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

    • Marked as answer by msimo1 Saturday, April 23, 2016 7:17 PM
    Saturday, April 23, 2016 7:17 PM
  • A better way to express the same would be:

     SELECT DISTINCT UI.UID
     ,UI.Country
     ,UI.State
     ...
     FROM  UserInfo AS UI
     WHERE EXISTS (SELECT *                FROM   ads                WHERE  AD.UID = UI.UID                  AND  CHARINDEX(@Wtag, ads.Wtags) > 0)
     WHERE UI.Country = @Location    AND UI.UsrType = 'Business'
     ORDER BY UI.RegDate DESC

    Behind the scenes this is likely to produce the same query plan as your query, but I think the above expresses the intention of the query better.

    Note also that I have remove the brackets - that helps to make the code easier to read in my opinion.

    • Proposed as answer by Naomi N Monday, April 25, 2016 4:48 PM
    Saturday, April 23, 2016 7:29 PM
  • Most likely you don't need DISTINCT in this query either if UID is the unique column in the UserInfo table. I was thinking along the same lines when I read that query the first time - as long as you exclude the wTags column from the final output you'll get correct info.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, April 25, 2016 4:50 PM