How to use Distinct Keyword for BusinessId Column?

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi

Here I have a procedure and using this procedure iam getting tha data from several columns but businessId column has several duplicates.how to get rid of the duplicates.
how to use distinct keyword here. some one help me.

SQL

ALTER procedure CORP.USP_GET_BUSINESSDETAILS_SEARCH
 (     @BusinessID INT = NULL
	,@BusinessName VARCHAR(255) = NULL
	,@PageID INT = NULL
	,@PageCount INT = NULL
)

as begin
DECLARE @StartFrom INT = (@PageID -1) * @PageCount
IF @PageID = 1 OR @PageID IS NULL
	BEGIN
SELECT TOP(@PageCount) B.BusinessId,B.BusinessName,B.CreatedDate,BS.[Description] as Status,CONCAT(A.StreetAddress1,',',A.StreetAddress2,',',A.City,',',A.State,',',A.Zip5,',',Zip4) as PrincipalOfficeAddress, B.EmailAddress,B.DateOfIncorporation 
 from [CORP].[Business] as B
 inner join [CORP].[BusinessXAddress] as BA on B.BusinessId=BA.BusinessId
 inner join [CORP].[Address] as A on BA.AddressId=A.AddressId  
 Inner join [CORP].[BusinessStatus] as BS on BS.BusinessStatusId = B.BusinessStatusId
 WHERE   (@BusinessName IS NULL OR B.BusinessName LIKE @BusinessName + '%' ) 
 ORDER BY B.BusinessID
 SELECT COUNT(1) AS TotalRowCount
		FROM CORP.Business B WITH(NOLOCK)
		WHERE (@BusinessID IS NULL OR B.BusinessID = @BusinessID)
		AND (@BusinessName IS NULL OR BusinessName LIKE '%' + @BusinessName + '%')
END
	ELSE
	BEGIN
		SELECT Distinct B.BusinessID,B.BusinessName, BT.BusinessTypeDesc AS BusinessType,BS.[Description] AS [Status],CONCAT(A.StreetAddress1,',',A.StreetAddress2,',',A.City,',',A.[State],',',A.Zip5,',',Zip4) as PrincipalOfficeAddress, B.EmailAddress, B.DateOfIncorporation  
		FROM CORP.Business B WITH(NOLOCK)
		INNER JOIN CORP.BusinessType BT ON BT.BusinessTypeId = B.BusinessTypeId
		inner join [CORP].[BusinessXAddress] as BA on B.BusinessId=BA.BusinessId
		inner join [CORP].[Address] as A on BA.AddressId=A.AddressId  
		INNER JOIN CORP.BusinessStatus BS ON BS.BusinessStatusId= B.BusinessStatusId
		WHERE (@BusinessID IS NULL OR B.BusinessID = @BusinessID)
		AND (@BusinessName IS NULL OR BusinessName LIKE '%' + @BusinessName + '%')
		ORDER BY B.BusinessID
		OFFSET @StartFrom ROWS FETCH NEXT @PageCount ROWS ONLY
	END
END

Thanks in Advance

Posted 2-Apr-15 0:19am

raxhemanth

Updated 2-Apr-15 2:01am

Herman<T>.Instance

v3

Add a Solution

Comments

Herman<T>.Instance 2-Apr-15 7:58am

Is BusinessID the PK?

Herman<T>.Instance 2-Apr-15 8:01am

Which of the 2 queries is your problem?

Herman<T>.Instance 2-Apr-15 8:03am

if @PageID IS NuLL what is @StartFrom for value?

Suvendu Shekhar Giri 2-Apr-15 9:02am

BusinessId can be duplicate but are two or more rows have same data , meaning duplicated data for all the columns? if not, then you should not. Athough there are ways to do this but I am sure you will end up with incorrect result. So, first of all make sure that you are selecting only required columns and no additional columns.

raxhemanth 2-Apr-15 9:29am

YES BusinessId is PK

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Herman<T>.Instance · Answer 1 · 2015-04-02T03:04:00

Try this (query can have bad performance):

SQL

SELECT DISTINCT TOP(@PageCount) *
FROM (SELECT B.BusinessID,B.BusinessName, BT.BusinessTypeDesc AS BusinessType,BS.[Description] AS [Status],CONCAT(A.StreetAddress1,',',A.StreetAddress2,',',A.City,',',A.[State],',',A.Zip5,',',Zip4) as PrincipalOfficeAddress, B.EmailAddress,
    B.DateOfIncorporation
    FROM CORP.Business B WITH(NOLOCK)
    INNER JOIN CORP.BusinessType BT ON BT.BusinessTypeId = B.BusinessTypeId
    inner join [CORP].[BusinessXAddress] as BA on B.BusinessId=BA.BusinessId
    inner join [CORP].[Address] as A on BA.AddressId=A.AddressId
    INNER JOIN CORP.BusinessStatus BS ON BS.BusinessStatusId= B.BusinessStatusId
    WHERE (@BusinessID IS NULL OR B.BusinessID = @BusinessID)
    AND (@BusinessName IS NULL OR BusinessName LIKE '%' + @BusinessName + '%')
    ORDER BY B.BusinessID
    OFFSET @StartFrom ROWS)  as x