CODEWITHSUNDEEP
sqlms-accessselectfielddistinct
Caesium95
Caesium95

Reputation: 579

SQL How to select distinct value from multiple fields

In the database(of tutor profiles), each tutor can teach up to 3 subjects, hence I've created 3 fields--Subject1,Subject2 & Subject3-- for each tutor. In my case, I would like to retrieve each specific subject within the 3 fields and add them to my combo box in my program for a criteria searching function.

Initially, I used the following code for 3 different fields : 

Dim sqlSubjectComboBox As String = "SELECT DISTINCT [TutorSubject1] FROM tblTutor"
Dim cmdSubjectComboBox As New OleDbCommand(sqlSubjectComboBox, myConnection)
dr = cmdSubjectComboBox.ExecuteReader
While dr.Read()
    cbSubject.Items.Add(dr("TutorSubject1").ToString)
End While

However, I realised that this sql statement will create a logic error if a same subject is placed in different field for different tutor.

Ex : Tutor A has the subject 'Chemistry' on his field Subject1. While for Tutor B, he has the same subject 'Chemistry' on field Subject2. In the end, the combo box has two 'Chemistry'.

I had spent almost a day to figure this out but to avail, partly due to my subpar programming skill and lack of experience. Hopefully someone can help me out, thanks in advance!

Upvotes: 0

Views: 537

Answers (1)

Rhys Jones
Rhys Jones

Reputation: 5498

You can use the UNION operator to get the distinct list of subjects.

select TutorSubject1 FROM tblTutor where TutorSubject1 is not null
union
select TutorSubject2 FROM tblTutor where TutorSubject2 is not null
union
select TutorSubject3 FROM tblTutor where TutorSubject3 is not null

The important point here is that the UNION operator removes duplicates.

Upvotes: 2

Related Questions