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

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.

Real Results Spread(Field, 'Count')

Real Results Spread(Field, 'Percent')

Real Result Spread(Field, 'Spread')

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