How can I create a column whose entries depend on the contents of a column in another table?-CodePudding

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".

CodePudding user response：

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 the Properties_Owned table and can just add a column Player_id (or owned_by_player_id) to the Properties table as foreign key referencing the Players table.

alter table Properties 
  add column Player_id int default null,
  add foreign key (Player_id) references Players(id);

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:

select 
  p.*,
  case when Player_id is null then 'No' else 'Yes' end as Owned
from Properties p;

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.

alter table Properties 
  drop column Owned,
  add column Owned varchar(3) 
    as (case when Player_id is null then 'No' else 'Yes' end) virtual;

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.

CodePudding user response：

You could create a view that extends your table by the column you want. There you can use a CASE statement.

CASE WHEN id IN SELECT Prop_id FROM properties_owned THEN 'Yes' ELSE 'No'

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:

UPDATE Prop_id SET properties_owned = CASE WHEN id IN SELECT Prop_id FROM properties_owned THEN 'Yes' ELSE 'No' WHERE