MySQL replaces the value from each record

Question

I have a table in my database that looks like this:

==========
| id | code |
==========
| 1 | a |
==========
| 2 | e |
==========
| 3 | r |
==========

etc. I would like to be able to make a request that will tell me which letters in the alphabet are not in the table.

I initially thought of such a query:

SELECT REPLACE ('abcdefghijklmnopqrstuvwxyz', (SELECT code FROM table), '');

and hoping to output 'defghijklmnopqrstuvwxyz', and I could just blow it up into an array of unused characters. Unfortunately, MySQL REPLACE does not allow the use of a table of values.

Any suggestions for creating this list?

Answer 1

As I can recommend , this is to create a table with a row for each letter in the alphabet, which will make it easier to delete letters with LEFT JOIN and GROUP_CONCAT at the end.

Without this table, you have to resort to hacking by creating a temporary “alphabet table” using a JOIN between a temporary variable and a table with more rows than the number of letters. In this example, I am using INFORMATION_SCHEMA.COLLATIONS.

SELECT GROUP_CONCAT(CHAR(ch) SEPARATOR '') 'missing letters' FROM (SELECT @tmp: =@tmp +1 ch FROM (SELECT @tmp:=96) a, INFORMATION_SCHEMA.COLLATIONS WHERE @tmp<122) z LEFT JOIN TableA ON ch=ORD(TableA.code) WHERE TableA.code IS NULL; 

A nested SELECT constructs an alphabetical sequence; LEFT JOIN deletes the letters that exist in TableA (your table). GROUP_JOIN combines the received letters. The result is a string with all the letters that do not exist in TableA.

Answer 2

Build server-side logic. Create a query to get all used letters (separate), then go through the result set and clear the used characters from a possible array / string.