Android Question [SOLVED] SQL Select Distinct from 2 Columns

mangojack

Expert
Licensed User
Longtime User
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)
B4X:
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 ...
B4X:
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
 
Last edited:
Solution
Try so:
B4X:
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

Expert
Licensed User
Longtime User
Try so:
B4X:
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
 
Upvote 0
Solution

mangojack

Expert
Licensed User
Longtime User
Thank you The second set of code passing duplicate profile string worked ..
 
Upvote 0

JohnJ

Member
Licensed User
Longtime User
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)
B4X:
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 ...
B4X:
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)
B4X:
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 ...
B4X:
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
 
Upvote 0

mangojack

Expert
Licensed User
Longtime User
This query (select distinct F01||F02 as x from T01) results in
B4X:
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.
B4X:
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 ....
Capture2.PNG


Thanks Again.


 

Attachments

  • Capture2.PNG
    Capture2.PNG
    2.3 KB · Views: 21
Upvote 0
Top