I have a database with multiple tables, and I want to add a column to one table that will be populated with different strings based on the contents of another table.
Below are the tables of interest.
CREATE TABLE Locations(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Location VARCHAR(17) NOT NULL,
Is_Property BOOLEAN NOT NULL
);
CREATE TABLE Players(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Player_Name VARCHAR(17) NOT NULL,
Token VARCHAR(17) NOT NULL,
FOREIGN KEY (Token) REFERENCES Tokens(Token),
P_Location VARCHAR(17) NOT NULL,
FOREIGN KEY (P_Location) REFERENCES Locations(Location),
Bank_Balance INT NOT NULL DEFAULT 200);
ALTER TABLE Locations ADD INDEX `Location` (`Location`);
CREATE TABLE Properties AS SELECT id,Location FROM Locations
WHERE Is_Property = 1;
ALTER TABLE Properties
ADD CONSTRAINT PK_Properties PRIMARY KEY (id),
ADD COLUMN Colour VARCHAR(6),
ADD COLUMN Cost_And_Rent INT,
ADD COLUMN Owned VARCHAR(3);
CREATE TABLE Properties_Owned(
Player_id INT NOT NULL,
Prop_id INT NOT NULL,
PRIMARY KEY(Player_id, Prop_id),
FOREIGN KEY (Player_id) REFERENCES Players(id),
FOREIGN KEY (Prop_id) REFERENCES Properties(id));
The Properties
and Properties_Owned
tables are of interest in this case. I want to create a column called Owned
in Properties
and populate it with "Yes" or "No" based on if the primary key appears under Prop_id
in Properties_Owned
. Ergo if it does, Properties.Owned
will show "Yes", and if not, "No".
I’ve tried using the CASE
function, but I’m unsure of if it can be used without calling a SELECT
query. Below is my last attempt to do so, but the syntax is wrong somewhere or just misguided altogether.
CASE
WHEN id IS IN properties_owned.Prop_id THEN Properties.Owned = "Yes"
ELSE "No" ;
It generates the error code:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE WHEN id IS IN properties_owned.Prop_id THEN Properties.Owned = "Yes" ELSE ' at line 1
Edit: As there was a request for sample data, here it is:
INSERT INTO Locations(Location,Is_Property) VALUES ("GO", 0),
("London", 1),
("Paris", 0),
("China", 1),
("New Zealand", 0),
("Sydney", 1),;
INSERT INTO Players(Player_Name,Token,P_Location,Bank_Balance) VALUES
("Mary","Battleship","London",190),
("Bill","Dog","Paris",500),
("Jane","Car","China",150),
("Norman","Thimble","London",250);
INSERT INTO Properties_Owned(Player_id,Prop_id) VALUES
(1,1),
(2,2),
(3,3),
(4,4),
(4,5);
Thus the Properties_Owned table will look like this:
Player_id | Prop_id
----------|---------
1 |1
2 |2
3 |3
4 |4
4 |5
And so in the Properties
table under Owned
, if Properties.id
appears in Prop_id
above, the Owned
column should yield "Yes".
2
Answers
You could create a view that extends your table by the column you want. There you can use a CASE statement.
If this is not what you want, you could possibly use triggers on both tables that fill/update the column on on create/on delete
Initially you could fill the column with something like this:
The table
Properties_Owned
reflects an (n:n)-relation (many-to-many). But according to your comment, a property cannot be owned by multiple players at the same time. And that would be an (n:1)-relation (many-to-one). In that case you do not need theProperties_Owned
table and can just add a columnPlayer_id
(orowned_by_player_id
) to theProperties
table as foreign key referencing thePlayers
table.Then the information required for the
Owned
column will be already in the same table. You just need to "manipulate" it in your SELECT statements. For example with:No need to store the same information redundantly. That would "bite" you sooner or later. Avoid redundancy when possible.
If your MySQL version (5.7+ required) supports Generated Columns you can also let the database maintain the redundancy.
Now the (genrated) column is dependent on
Player_id
column and you don’t need (and cannot) store anything there but can select it.virtual
means that it is not stored but generated (on the fly) when it’s needed. Now you can read it in your queries as if it is normal column.See example on db-fiddle.com
And again: Avoid redundant data when possible. At least use foreign keys to avoid data inconsistency.