[RESOLVED] Consufed on a Select Statement?

[GaryMazzone]

I have the following query :

SELECT Personnel.PersonnelPK,Personnel.LastName + ', ' + Personnel.FirstName + ' ' + Personnel.MiddleName AS 'Personnel Name',WorkerTypes.CustumLevelName AS 'Worker Type',Location.Building + ' - ' + Location.LocationName As [Location]  FROM Location INNER JOIN PersonnelToLocation ON Location.LocationPK = PersonnelToLocation.LocationPK RIGHT OUTER JOIN Personnel INNER JOIN WorkerTypes ON Personnel.WorkerTypePK = WorkerTypes.WorkerTypePK ON PersonnelToLocation.PersonnelPK = Personnel.PersonnelPK Where Personnel.PersonnelPK > 1  And Personnel.PersonnelPK In (Select PersonnelPK From PersonnelToLocation Where PersonnelPK = 3)

The query works, well kind of works. If a person is assigned to more then one location I get the persons name multiple times. Any sugestions as to getting the personns name only once with multile locations on one line?

This is being returned as a dataset on .Net 2005 from and SQL Server 2005 backend.

[kaffenils]

The query works, well kind of works. If a person is assigned to more then one location I get the persons name multiple times. Any sugestions as to getting the personns name only once with multile locations on one line?

Since the result from the query contains data from the Location table, what do you want to display when a person is assigned to more than one location? The first location? A concatenated string with all locations?

[GaryMazzone]

All locations conatinated togther with the person's name in the result set once.

[kaffenils]

First of all you must clean up the query.

This one makes no sense. You return the PersonnelPK from a table where PersonnelPK=3. Well, that will always return 3.

And Personnel.PersonnelPK In (Select PersonnelPK From PersonnelToLocation Where PersonnelPK = 3)

Can you explain what you want the query to do. Do you want to return all persons for a specific location? Please explain; especially the conditions for what data to be returned.

[GaryMazzone]

The code that generates is as follows:

Private Sub loadPersonnelGrid()
        Dim strSQL As String = String.Empty
        strSQL = "SELECT Personnel.PersonnelPK,Personnel.LastName + ', ' + Personnel.FirstName + ' ' + Personnel.MiddleName AS 'Personnel Name',"
        strSQL &= "WorkerTypes.CustumLevelName AS 'Worker Type',Location.Building + ' - ' + Location.LocationName As [Location]  "
        strSQL &= "FROM Location INNER JOIN "
        strSQL &= "PersonnelToLocation ON Location.LocationPK = PersonnelToLocation.LocationPK RIGHT OUTER JOIN "
        strSQL &= "Personnel INNER JOIN "
        strSQL &= "WorkerTypes ON Personnel.WorkerTypePK = WorkerTypes.WorkerTypePK ON PersonnelToLocation.PersonnelPK = Personnel.PersonnelPK "
        strSQL &= "Where Personnel.PersonnelPK > 1 "
        If Me.optHideInact.Checked Then
            strSQL &= "And Personnel.WorkerTypePK <> 2 "
        End If
        If Me.cboLocations.SelectedIndex > 0 Then
            If Me.cboLocations.SelectedIndex = 1 Then
                strSQL &= " And Personnel.PersonnelPK Not In (Select PersonnelPK From PersonnelToLocation) "
            Else
                strSQL &= " And Personnel.PersonnelPK In (Select PersonnelPK From PersonnelToLocation Where LocationPK = " & Me.arintLocationPKs(Me.cboLocations.SelectedIndex).ToString() & ") "
            End If
        End If
        strSQL &= "Order By LastName,FirstName,MiddleName"
 
        Dim oDS As System.Data.DataSet
        If Not mdlGeneral.currUser.bAccessToAdmin Then
            Me.cmdNew.Enabled = False
        End If
 
        da = New clsDataAccess
        oDS = da.Return_DataSet(strSQL)
 
        If Not oDS Is Nothing Then
            Me.dtGridPersonnel.DataSource = oDS.Tables(0)
            Me.dtGridPersonnel.Columns(0).Visible = False
            For i As Integer = 1 To Me.dtGridPersonnel.ColumnCount() - 1
                Me.dtGridPersonnel.Columns(i).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            Next
            Me.dtGridPersonnel.AllowUserToDeleteRows = False
            Me.dtGridPersonnel.AllowUserToAddRows = False
            oDS.Dispose()
        Else
            Me.dtGridPersonnel.Visible = False
        End If
        Me.dtGridPersonnel.Visible = True
        If mdlGeneral.errMessage.intErrNum <> 0 Then
            MessageBox.Show(mdlGeneral.errMessage.strMess, "Data Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
        da.Dispose()
    End Sub

da is a class defined as private on the form and is a data access class

The query will return results based on a copule of controls on the form. A radiobutton to remove inactive personnel, an Checkbox that displays a list of locations if you only want to see personnel from one location only.

So if no location is selected then you get all personnel and the locations they are assigned in the dataset.

So when the form first loads you get an SQL statement that looks like this:

SELECT Personnel.PersonnelPK,Personnel.LastName + ', ' + Personnel.FirstName + ' ' + Personnel.MiddleName AS 'Personnel Name',WorkerTypes.CustumLevelName AS 'Worker Type',Location.Building + ' - ' + Location.LocationName As [Location]  FROM Location INNER JOIN PersonnelToLocation ON Location.LocationPK = PersonnelToLocation.LocationPK RIGHT OUTER JOIN Personnel INNER JOIN WorkerTypes ON Personnel.WorkerTypePK = WorkerTypes.WorkerTypePK ON PersonnelToLocation.PersonnelPK = Personnel.PersonnelPK Where Personnel.PersonnelPK > 1 Order By LastName,FirstName,MiddleName