MySQL get attribute which is not present for all possible values of another attribute

Assume there is a table with columns fieldA and fieldB (Primary Key:fieldA, fieldB). A program keeps inserting entries to this table with the same fieldA value, say constant_for_A, and some arbitrary value for fieldB. In its consistent state, all fieldA values are related to all possible fieldB values. To ensure this, I am trying to write a procedure that will check which values of fieldB might not have been mapped to values from fieldA and insert the corresponding row into the table.

As an example, the state of the table, before a new insertion and after the procedure runs are:

1. State 1: No insertions

fieldA	fieldB
A	X
B	X
C	X

2.State 2: After Insertion (fixed value for fieldA, arbitrary value for fieldB)

fieldA	fieldB
A	X
B	X
C	X
A	Y

3. State 3: After the procedure runs

fieldA	fieldB
A	X
B	X
C	X
A	Y
B	Y
C	Y

Once again, whenever a new value for fieldB is added, it is always done for a fixed, known value of fieldA.

I have tried using joins and nested select queries, however, I can’t figure out the correct approach to writing such a procedure. How could this be achieved in MySQL

Answer

There’s no ‘checking’ required, and nothing ‘procedural’ here. A simple query will suffice:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(fieldA CHAR(1) NOT NULL
,fieldB CHAR(1) NOT NULL
,PRIMARY KEY(fieldA,fieldB)
);

INSERT INTO my_table VALUES
('A','X'),
('B','X'),
('C','X'),
('A','Y');

INSERT IGNORE INTO my_table SELECT x.fielda, y.fieldb FROM my_table x, my_table y;

SELECT * FROM my_table;
+--------+--------+
| fieldA | fieldB |
+--------+--------+
| A      | X      |
| A      | Y      |
| B      | X      |
| B      | Y      |
| C      | X      |
| C      | Y      |
+--------+--------+