Generate list of values found in a column of a database table

[MSPhobe]

I have a database table with a field that contains a short string of letters. There are a few (20?) different strings that are present many, many times.

I am hoping there’s a way to, in loose, non “database” terms, “scan” the column, make a list containing one instance of each string that occurs. In a perfect world, a technique would be available to find ALL of the strings in either or both of two columns, but a “do each colums separately” solution will do the job. The attached image gives a taste what I’m talking about, I hope…

From that I’m hoping for tabulation saying, in effect, “The following appear: SBCa, Sold, Exch, CS1, CBAg…”

I don’t need to know which appeared in which of the two inspected columns.

Query? Report? What? (I hope that with a little guidance I could implement a solution. I just drew a blank when I tried to think of an approach.) Prefered environment: Libre Office Base using embedded Firebird.

[Ratslinger]

Hello,

Using Firebird embedded:

“SearchList” is the table name used in the SQL. Just selected the distinct items from each column, then used that for input as the list of distinct form all items.

Selecting the Distinct for each column first is not necessary. You can select all from each and then select Distinct.

[MSPhobe]

Brilliant… and thank you… has given me HOPE. Worked!

Ummm… apologies… I didn’t ask question well.

Can your solution be modified to create something that creates a new table, with the distinct values in one of the fields of the records of the table? Sorry. (I HAVE TRIED to find modification on my own,)

[Ratslinger]

You create a table (CREATE TABLE or How to create an auto-increment ID column in a Firebird embedded database table? Errors encountered) and then insert the record (INSERT)

From the main Base menu Tools->SQL I did this:

CREATE TABLE "my_list" (
"id"  INTEGER generated by default as identity primary key,
"the_list" VARCHAR(256)
);

then

insert into "my_list" ("the_list") Select LIST(Distinct("Term")) from
(Select "from" "Term" from "SearchList"
Union
Select "to" "Term" from "SearchList")

Or if you want each value in its’ own record:

insert into "my_list" ("the_list") Select Distinct("Term") from
(Select "from" "Term" from "SearchList"
Union
Select "to" "Term" from "SearchList")

[MSPhobe]

Very helpful! Thank you! I am much further along with understanding this than I was.

What would the proper way be to say what follows… assuming it is even close to being correct!!!
(I’m fairly happy with the breakdown into parts… Maybe it could be improved, but the bit I am particularly seeking help with is the later bits where I talk about the “Term” bit.)

---

This example assumes that your database has a table called “my_list”
… and that my_list has a field (aka column) called “the_list”)
AND it assumes you have a table called “SearchList”, which has a field called “from”

---

To understand the “Or if you want each value in its’ own record…” solution, look at it like this.

It is in three parts… the “outer” part is…

INSERT INTO "my_list" ("the_list") XXX

where XXX is…

SELECT DISTINCT ("Term") FROM YYY

where YYY is…

(SELECT "from" "Term" FROM "SearchList"
UNION
SELECT "to" "Term" FROM "SearchList")

(Itself made of two parts!)

---

Now… looking at that in reverse order. “Bottom up”, as it is called…

Taking just one of the parts of “YYY”…

‘SELECT “to” “Term” FROM “SearchList”’

That returns (important idea) some values.

If the table “SearchList” had just four records, and the values in the “to” fields were…

Sold
Exch
Sold
AGIn

… then SELECT "to" "Term" FROM "SearchList" would return…

Sold
Exch
Sold
AGIn

If you put SELECT "to" "Term" FROM "SearchList" into the SQL command-line interface, you’d get that short list of values “out” as the “output” of the command. Even if you include the "Term" bit, which is, for now, superfluous. (You get that short list if you remember to “tick” the “Show output of “Select” statements” box on the command line dialog! (^_^))

But when we are doing it “inside” some other SQL, with the “Term” part, the values returned by this part of the SQL command don’t “appear” anywhere, immediately. They “go to” a “virtual”/“temporary”/“internal” “mini-table”. And we can refer to that by the name “Term”. (Which isn’t a “special” name. We could call it “XYZ123”… if we were of a mind to. Or “TmpResults” if we wanted to be sensible. This “virtual mini-table” happens to have only one field, aka column. A different SELECT could create a “virtual mini-tables” with more than one field.

===
That’s the hard bit behind us!

Once we are clear on the idea of the “virtual mini-tables” with values in them, the next step of understanding the whole command is relatively easy.

YYY…

(SELECT "from" "Term" FROM "SearchList"
UNION
SELECT "to" "Term" FROM "SearchList")

now looks like…

Make two “virtual mini-tables”

Combine them by the “UNION” rule. It creates a single “virtual mini-table”, and returns THAT to the next “higher” level of the overall command.

(The “UNION rule” says in the result, you have one copy of anything that appeared in either of the two lists of values supplied. Just one copy, even if a particular value appears in both sets of values, or if a value appeared more than once in one of the two sets which were combined.)

Now we go “up” again…

where XXX is…

SELECT DISTINCT ("Term") FROM YYY

We’ve seen, above, that by now “YYY” has been “boiled down” to just a list of values.

XXX goes through them, and creates a new “virtual mini-table”, and returns that to the next higher level…

INSERT INTO "my_list" ("the_list") XXX

… which adds some rows to the (real, not virtual) table called my_list.

Those rows will have the values which were in the latest “Term” “virtual mini-table” in their “the_list” fields.

---

A detail: The “DISTINCT” element says “throw away any duplicate values in the list being supplied”.

I THINK “UNION” has already thrown away duplicates, but the DISTINCT does no harm (/ isn’t necessary??)

=====
And that’s “all” there is to it? (^_^)

[Ratslinger]

@MSPhobe

@MSPhobe

I THINK “UNION” has already thrown away duplicates,

No. Union has nothing to do with duplicates. That is distinct and even if used in the Union section it is still needed in the main section because duplicated can result from the combined two columns. “Term” is an arbitrary renaming of the column to be consistent with both select statements in the Union section.

Otherwise with all you stated, not clear if there is some question there.

[MSPhobe]

Many thanks… not for the first time!

Any necessary things like your correction of my error in respect of “UNION” was the main thing.

The “question”, to put it another way, was “Are there errors in this attempt to put what I have understood (from reading answers) into words?”

The other “question” was "Is there a better way to speak of what I called the “virtual mini-table?”, but if it isn’t too far from the way things are, what I’ve done will do for my wants.

[Ratslinger]

@MSPhobe

The other “question” was "Is there a better way to speak of what I called the “virtual mini-table?”

It is a way to think of it but not what happens - that may consume considerable time. It more like what is eligible for processing.

This is something more for an SQL forum. Could not find much for Firebird but here is something from Oracle → SQL Processing