Android Question [SOLVED] SQL Select Distinct from 2 Columns

[mangojack]

I wish to get SINGLE DISTINCT values from a Combination of 2 Database Columns .
The Columns (Tag1 & Tag2) contain string values(Tags). There is duplicated tags within each colum and duplicated tags between / in both columns.

The query results are added to a Single Tag List which is to contain only One Single Occurrence of each individual tag value.

This First attempt resulted in duplicate Tags... (I kind of understand why this is failing.. Distinct in Tag2 , so Tag1 (duplicate) is also added... maybe)

Dim rs As ResultSet
    rs = oSQL.ExecQuery2("SELECT DISTINCT Tag1,Tag2 FROM tblHistory WHERE Person = ?", Array As String(profile))
    Do While rs.NextRow
        If rs.GetString("Tag1") <> "" Then lstTags.Add(rs.GetString("Tag1"))
        If rs.GetString("Tag2") <> "" Then lstTags.Add(rs.GetString("Tag2"))
    Loop

After more research , this was the suggested method / query ...

Dim rs As ResultSet
    rs = oSQL.ExecQuery2("SELECT DISTINCT Tag1 From tblHistory UNION  SELECT DISTINCT Tag2 FROM tblHistory WHERE Person = ?", Array As String(profile))
    Do While rs.NextRow
        If rs.GetString("Tag1") <> "" Then lstTags.Add(rs.GetString("Tag1"))
        If rs.GetString("Tag2") <> "" Then lstTags.Add(rs.GetString("Tag2"))
    Loop

But this only results in an Error ... Caused by: java.sql.SQLException: no such column: 'Tag2'

Without question there is a column "Tag2" . It contains Tag values and I can log its contents with the First query method above.

Is the 2nd Query Correct , if so why the "no such column error" ?
Am I querying the returned result set correctly ?

But, at the end of the day , if there is a better way of storing 1 or 2 "Tags" for each DB record , and being able to populate a list with Single Unique Tags I am all ears.

Thanks & Regards

 

[LucaMs]

Try so:

Dim Query As String
Query = "SELECT DISTINCT UniqueTags FROM (Select Tag1 As UniqueTags FROM tblHistory WHERE Person = ? UNION ALL Select Tag2 As UniqueTags FROM tblHistory WHERE Person = ?) As AllTags ORDER BY UniqueTags"

Dim rs As ResultSet
rs = oSQL.ExecQuery2(Query, Array As String(profile, profile))
Do While rs.NextRow
    If rs.GetString("UniqueTags") <> "" Then lstTags.Add(rs.GetString("UniqueTags"))
Loop

 

[LucaMs]

Using smart strings makes the query more readable and understandable.

Dim Query As String

Query = _
$"SELECT DISTINCT UniqueTags
  FROM (
 
      Select Tag1 As UniqueTags
      FROM tblHistory WHERE Person = ?
 
        UNION ALL
   
      Select Tag2 As UniqueTags
      FROM tblHistory WHERE Person = ?
 
  ) As AllTags

ORDER BY UniqueTags"$

 

[mangojack]

Thank you The second set of code passing duplicate profile string worked ..

 

[JohnJ]

I wish to get SINGLE DISTINCT values from a Combination of 2 Database Columns .
The Columns (Tag1 & Tag2) contain string values(Tags). There is duplicated tags within each colum and duplicated tags between / in both columns.

The query results are added to a Single Tag List which is to contain only One Single Occurrence of each individual tag value.

This First attempt resulted in duplicate Tags... (I kind of understand why this is failing.. Distinct in Tag2 , so Tag1 (duplicate) is also added... maybe)

Dim rs As ResultSet
    rs = oSQL.ExecQuery2("SELECT DISTINCT Tag1,Tag2 FROM tblHistory WHERE Person = ?", Array As String(profile))
    Do While rs.NextRow
        If rs.GetString("Tag1") <> "" Then lstTags.Add(rs.GetString("Tag1"))
        If rs.GetString("Tag2") <> "" Then lstTags.Add(rs.GetString("Tag2"))
    Loop

After more research , this was the suggested method / query ...

Dim rs As ResultSet
    rs = oSQL.ExecQuery2("SELECT DISTINCT Tag1 From tblHistory UNION  SELECT DISTINCT Tag2 FROM tblHistory WHERE Person = ?", Array As String(profile))
    Do While rs.NextRow
        If rs.GetString("Tag1") <> "" Then lstTags.Add(rs.GetString("Tag1"))
        If rs.GetString("Tag2") <> "" Then lstTags.Add(rs.GetString("Tag2"))
    Loop

But this only results in an Error ... Caused by: java.sql.SQLException: no such column: 'Tag2'

Without question there is a column "Tag2" . It contains Tag values and I can log its contents with the First query method above.

Is the 2nd Query Correct , if so why the "no such column error" ?
Am I querying the returned result set correctly ?

But, at the end of the day , if there is a better way of storing 1 or 2 "Tags" for each DB record , and being able to populate a list with Single Unique Tags I am all ears.

Thanks & Regards

I don't have any data to play

I wish to get SINGLE DISTINCT values from a Combination of 2 Database Columns .
The Columns (Tag1 & Tag2) contain string values(Tags). There is duplicated tags within each colum and duplicated tags between / in both columns.

The query results are added to a Single Tag List which is to contain only One Single Occurrence of each individual tag value.

This First attempt resulted in duplicate Tags... (I kind of understand why this is failing.. Distinct in Tag2 , so Tag1 (duplicate) is also added... maybe)

Dim rs As ResultSet
    rs = oSQL.ExecQuery2("SELECT DISTINCT Tag1,Tag2 FROM tblHistory WHERE Person = ?", Array As String(profile))
    Do While rs.NextRow
        If rs.GetString("Tag1") <> "" Then lstTags.Add(rs.GetString("Tag1"))
        If rs.GetString("Tag2") <> "" Then lstTags.Add(rs.GetString("Tag2"))
    Loop

After more research , this was the suggested method / query ...

Dim rs As ResultSet
    rs = oSQL.ExecQuery2("SELECT DISTINCT Tag1 From tblHistory UNION  SELECT DISTINCT Tag2 FROM tblHistory WHERE Person = ?", Array As String(profile))
    Do While rs.NextRow
        If rs.GetString("Tag1") <> "" Then lstTags.Add(rs.GetString("Tag1"))
        If rs.GetString("Tag2") <> "" Then lstTags.Add(rs.GetString("Tag2"))
    Loop

But this only results in an Error ... Caused by: java.sql.SQLException: no such column: 'Tag2'

Without question there is a column "Tag2" . It contains Tag values and I can log its contents with the First query method above.

Is the 2nd Query Correct , if so why the "no such column error" ?
Am I querying the returned result set correctly ?

But, at the end of the day , if there is a better way of storing 1 or 2 "Tags" for each DB record , and being able to populate a list with Single Unique Tags I am all ears.

Thanks & Regards

So I have a sqlite table (T01) consisting of columns F01, F02
F01 F02
1 3
1 2
1 3
1 4
This query (select distinct F01||F02 as x from T01) results in
13
12
14
You would have to split the result if needed.
Documentation says that the concat keyword is valid but I didn't see that

 

[mangojack]

This query (select distinct F01||F02 as x from T01) results in

Query = "SELECT DISTINCT Tag1 || Tag2 AS UniqueTags FROM tblHistory WHERE Person = ?"
    Dim rs As ResultSet
    rs = oSQL.ExecQuery2(Query, Array As String(profile))
    Do While rs.NextRow
        If rs.GetString("UniqueTags") <> "" Then lstTags.Add(rs.GetString("UniqueTags"))
    Loop

Running your suggestion results in values from Tag1 & Tag2 Columns being joined and added to the list. This is not what I desire,
But thanks anyway.


I have been toying with @LucaMs Query and the following version still works as required.

Query = $"SELECT DISTINCT UniqueTags FROM (Select Tag1 As UniqueTags FROM tblHistory WHERE Person = ? 
                       UNION ALL Select Tag2 As UniqueTags FROM tblHistory WHERE Person = ?)"$
rs = oSQL.ExecQuery2(Query, Array As String(profile, profile))

I removed this portion... As AllTags ORDER BY UniqueTags as the results are added to a list which is sorted prior to populating a Combobox.


For extra clarity this is an example ....

Thanks Again.