How to "select DISTINCT" many tables in oledb in vb.net

December 4, 2022

I wanted to do the sql command "select DISTINCT" but it didn’t work if there was another solution. I have four tables namely GSDTS, GSGTS, STFTS and TEMPTABL.

Thanks

error

 Private Sub PopulateComboBox()
        Dim query As String = "SELECT DISTINCT PNM FROM GSDTS"
        Try
            Using con As OleDbConnection = New OleDbConnection(cn)
                Using sda As OleDbDataAdapter = New OleDbDataAdapter(query, con)
                    'Fill the DataTable with records from Table.
                    Dim dt As DataTable = New DataTable()
                    sda.Fill(dt)

                    'Insert the Default Item to DataTable.
                    Dim row As DataRow = dt.NewRow()

                    row(0) = ""
                    dt.Rows.InsertAt(row, 0)

                    'Assign DataTable as DataSource

                    ComboBox1.DataSource = dt
                    ComboBox1.DisplayMember = "PNM"
                    ComboBox1.ValueMember = "PNM"
                End Using
            End Using
        Catch myerror As OleDbException
            MessageBox.Show("Error: " & myerror.Message)
        Finally

        End Try
    End Sub

>Solution :

you neeed to cnage the query to

SELECT DISTINCT PNM FROM GSDTS
UNION
SELECT DISTINCT PNM FROM GSGTS
UNION
SELECT DISTINCT PNM FROM STFTS 
UNION
SELECT DISTINCT PNM FROM TEMPTAB
ORDER BY PMN

the DISINCT will only take UNIQUE OMN and the UNION will take care of all duplicates.

But you will so never know,. which PMN belongs to which table.