Retrieve data from two tables where table 2 has multiple entries for one value

Question

I am looking for the best way to create a SELECT statement to pull data from 2 tables. Example below is the table structures:

Table 1 | Table 2
------------------+----------------------------------
Table1.Customerid |
 | Table2.Customerid | Table2.Userid
 | Table2.Customerid | Table2.Userid

Basically, I have in table 2, multiple records showing assignment of multiple userids to one customerid. On the page this data displays, I'm getting duplicate Customer IDs because there are multiple entries for that customer id in table 2.

How do I display the customer ID only once even if there are multiple entries in table 2?

Here is a sample of the query currently being executed with an additional search parameter in place for the company name:

SELECT '' as campaigndaysremaining, COUNT(*) OVER () AS totalrecordcount, * 
FROM customers C 
LEFT OUTER JOIN customer_users CU on C.customerid = CU.customerid 
WHERE C.customerid > 0 
AND (C.company like '%blue%' OR c.altcompanyname like '%blue%') 
AND status = 0 
GROUP BY
 c.customerid,c.company,c.firstname,C.lastname,C.address1,C.address2,
 C.city,C.state,C.zip,C.country,C.phone,C.fax,C.cellphone,
 C.reference1,C.reference2,C.groupid,C.email,C.residential,C.status,
 C.datecreated,C.campaigndays, C.campaignstartdate, C.campaignenddate,
 C.lastupdate, C.lastupdateuser, C.campaignactive, C.customerstatus,
 C.regionid, C.categoryid,C.viewed,C.altcompanyname,C.url,cu.id,
 cu.customerid,C.lockaccount,cu.userid, cu.request, cu.regionid,
 cu.primaryrep, cu.dateadded 
ORDER BY C.company ASC

What I am looking for is this; no matter how many entries any customerid has in the customer_users table, I only want the distinct customer id's to be returned from the query. When a user searches the customers table, I don't want duplicate records in my gridview just because there are multiple users assigned to the customer.

Answer 1

Provided customer_users has ID and the order needed is customers.customerid , customer_users.ID you may ask sql server to show NULL in all but first row in a partition. Adjust ordering as needed.

SELECT '' AS campaigndaysremaining, COUNT(*) OVER () AS totalrecordcount, *,
 [show CustomerId]=CASE ROW_NUMBER() OVER (PARTITION BY C.customerid ORDER BY CU.ID) WHEN 1 THEN C.customerid END
FROM customers C 
LEFT OUTER JOIN customer_users CU ON C.customerid = CU.customerid 
WHERE C.customerid > 0
ORDER BY C.customerid , CU.ID

Getting only one user is a bit different task. First option is OUTER APPLY to get this only user

SELECT '' as campaigndaysremaining, * 
FROM customers C 
OUTER APPLY (
 SELECT TOP(1) cu.userid, cu.request, cu.regionid, cu.primaryrep, cu.dateadded ,
 COUNT(*) OVER () AS totalrecordcount
 FROM customer_users CU 
 WHERE C.customerid = CU.customerid 
 ORDER BY cu.userid -- change ordering as needed
) CU
WHERE C.customerid > 0 AND (C.company like '%blue%' OR c.altcompanyname like '%blue%') AND status = 0 
ORDER BY C.company ASC

• Jon Visconti: This did help me find my answer by going back through how my query was being created on the fly, dependent on multiple user selected inputs. By adding in an additional WHERE constraint, I was able to stop getting duplicate customer records when more than one user was assigned to a customer. Thanks to @Serg for taking the time to help me out.

Answer 2

My CLR SQL Server Aggregate Function (Spread)

These might not be the best example. What it does is list the unique values comma separated and can give you the occurrence count or percentage, considering this table.

Use these queries to get the corresponding results.

results

Real Results Spread(Field, 'Count')

Real Example 1

Real Results Spread(Field, 'Percent')

RealExample2

Real Result Spread(Field, 'Spread')

enter image description here

Source code

<Serializable(), SqlUserDefinedAggregate(Microsoft.SqlServer.Server.Format.UserDefined, _
 IsInvariantToDuplicates:=True, _
 IsInvariantToNulls:=False, _
 IsInvariantToOrder:=True, _
 IsNullIfEmpty:=True, _
 MaxByteSize:=-1, _
 Name:="Spread" _
 )> _
Public Structure Spread
 Implements IBinarySerialize
 Dim values As List(Of Value)
 Dim rawValues As List(Of String)
 Dim result As String
 Dim Count As Integer
 Dim HowMany As Integer
 Dim DisplayStyle As String
 Public Sub Init()
 Me.values = New List(Of Value)
 Me.rawValues = New List(Of String)
 result = String.Empty
 Count = -1
 HowMany = 1
 End Sub
 Public Sub Accumulate(ByVal value As SqlString, displayStyle As String)
 Me.HowMany = HowMany
 Me.DisplayStyle = displayStyle
 If value.IsNull Then
 Return
 End If
 Me.rawValues.Add(value.ToString())
 End Sub
 Public Sub Merge(ByVal group As Spread)
 Me.rawValues.AddRange(group.rawValues.ToArray())
 End Sub
 Public Function Terminate() As SqlString
 GetMostCommon()
 Dim e As Encoding = Encoding.Unicode
 Dim i As Integer = e.GetByteCount(result)
 'If i > 8000 Then
 ' Dim r() As String = result.Split(" : ".ToCharArray)
 ' Return New SqlString(r(0))
 'Else
 ' Return New SqlString(result)
 'End If
 If result.Length > 4000 Then
 Return New SqlString(Left(result, 3997) & "...")
 Else
 Return New SqlString(result)
 End If
 End Function
 Public Sub GetMostCommon()
 If HowMany < 1 Then
 HowMany = 1
 End If
 For Each s As String In rawValues
 AddOrIncrementValue(s)
 Next
 values.Sort()
 Dim x As Integer = 0
 Dim modeValue(values.Count - 1) As String
 Dim modeOccurences(values.Count - 1) As Integer
 For i As Integer = 0 To values.Count - 1 Step 1
 modeValue(i) = "N/A"
 modeOccurences(i) = 0
 Next
 For Each v As Value In values
 For z As Integer = 0 To values.Count - 1 Step 1
 If v.GroupOccurences = modeOccurences(z) Then
 modeValue(z) = modeValue(z) & ", " & v.GroupValue
 Exit For
 ElseIf v.GroupOccurences > modeOccurences(z) Then
 For i As Integer = values.Count - 1 To z + 1 Step -1
 modeValue(i) = modeValue(i - 1)
 modeOccurences(i) = modeOccurences(i - 1)
 Next
 modeValue(z) = v.GroupValue
 modeOccurences(z) = v.GroupOccurences
 Exit For
 End If
 Next
 Next
 result = values.Count.ToString() & " : "
 For i As Integer = 0 To values.Count - 1 Step 1
 If modeValue(i) = "N/A" Or modeValue(i) = "" Then
 Exit For
 Else
 Select Case DisplayStyle
 Case "Spread"
 result = result & IIf(i > 0, ", ", "").ToString() & modeValue(i)
 Case "Percent"
 result = result & IIf(i > 0, ", ", "").ToString() & modeValue(i) & " : " & Decimal.Round((modeOccurences(i) / Convert.ToDecimal(rawValues.Count)) * 100, 2) & "%"
 Case "Count"
 result = result & IIf(i > 0, ", ", "").ToString() & modeValue(i) & " : " & modeOccurences(i)
 End Select
 End If
 Next
 End Sub
 Public Sub AddOrIncrementValue(ByVal value As SqlString)
 Dim found As Boolean = False
 For Each v As Value In values
 If v.GroupValue = value.ToString() Then
 v.GroupOccurences += 1
 found = True
 Exit For
 End If
 Next
 If Not found Then
 values.Add(New Value(value.ToString()))
 End If
 End Sub
 Public Sub Read(ByVal r As System.IO.BinaryReader) Implements Microsoft.SqlServer.Server.IBinarySerialize.Read
 Me.result = r.ReadString()
 Me.Count = r.ReadInt32()
 Dim iCount As Integer = r.ReadInt32()
 'HowMany = r.ReadInt32()
 DisplayStyle = r.ReadString()
 rawValues = New List(Of String)
 Me.values = New List(Of Value)
 For i As Integer = 0 To iCount - 1 Step 1
 Me.rawValues.Add(r.ReadString())
 Next
 End Sub
 Public Sub Write(ByVal w As System.IO.BinaryWriter) Implements Microsoft.SqlServer.Server.IBinarySerialize.Write
 w.Write(result)
 w.Write(Me.Count)
 w.Write(rawValues.Count)
 'w.Write(HowMany)
 w.Write(DisplayStyle)
 For Each s As String In rawValues
 w.Write(s)
 Next
 End Sub
End Structure
Public Class Value
 Implements IComparable
 Public GroupValue As String
 Public GroupOccurences As Integer
 Public Sub New(ByVal GroupValue As String)
 Me.GroupValue = GroupValue
 GroupOccurences = 1
 End Sub
 Public Function CompareTo(obj As Object) As Integer Implements System.IComparable.CompareTo
 Dim s As Value = CType(obj, Value)
 'Return String.Compare(Me.GroupValue, s.GroupValue)
 If Me.GroupValue < s.GroupValue Then
 Return -1
 ElseIf Me.GroupValue > s.GroupValue Then
 Return 1
 Else
 Return 0
 End If
 End Function
End Class

Sorry for the all the commented code. I was making changes and forgot to cleanup.

This is added to make using the results of the Spread funtion easier.

<SqlFunction(DataAccess:=DataAccessKind.Read)> _
 Public Shared Function Split(Text As String, delimiter As String, returnZeroBasedIndex As Integer) As String
 Dim s() As String = VB.Split(Text, delimiter)
 If returnZeroBasedIndex <= s.Length - 1 Then
 Return s(returnZeroBasedIndex)
 Else
 Return ""
 End If
 End Function