I have a Model containing Propertyes.

So, I have a table Models, a Table Properties, and a table ModelProperties (ModelId, PropertyId, Value)

Actually when some new Property is/are added (in table Properties), I would like to update the ModelProperties table as well to add the newly added Properties to each of existing Models.

See the SQL Fiddle here

Should be careful cause some of the newly created Models could already have the new properties, so in order to update properly I need for each of the existing Model to

1. Select all new Properties to add to that Model (PropertyId exist in in Properties but not in ModelProperties for that ModelId)

2. Add to the Model the new Properties it does not have (with empty values).

Example: I have Model 1 and Model 2 with properties till 100. Two new properties were added: 101 and 102. Model 2 was updated to the property 101, but not yet 102. My selection should give/update. My sql should give me the models/properties to update:

ModelIdToUpdate PropertyIdToAdd

1               101
1               102

2               102

Q: What should be the sql script to give the result above?

SQL

CREATE TABLE "Models" (
  "Id" int NOT NULL,
  PRIMARY KEY ("Id")
);
CREATE TABLE "Properties" (
  "Id" int NOT NULL,
  PRIMARY KEY ("Id")
);
CREATE TABLE "ModelProperties" (
  "ModelId" int NOT NULL,
  "PropertyId" int NOT NULL,
  "Value" int NULL,
  PRIMARY KEY ("ModelId", "PropertyId")
);

INSERT INTO "Models"     ("Id") VALUES (1), (2);    
INSERT INTO "Properties" ("Id") VALUES (99), (100); -- existing    
INSERT INTO "Properties" ("Id") VALUES (101), (102); -- new
  
INSERT INTO "ModelProperties" ("ModelId", "PropertyId") VALUES
  (1, 99), (1, 100),
  (2, 99), (2, 100), (2, 101); -- Model 2 updated to 101```

the select

select * from Properties p
left join ModelProperties mp on 
mp.PropertyId = p.Id
where mp.PropertyId is NULL

CodePudding user response：

If you CROSS JOIN your models and your properties, you can then use NOT EXISTS to remove those that have a record in your ModelProperties table, then use this as the basis for inserting your empty properties:

INSERT INTO ModelProperties (ModelId, PropertyId)
SELECT  m.Id,
        p.Id
FROM    Properties p
        CROSS JOIN Models AS m
WHERE   NOT EXISTS 
        (   SELECT  1 
            FROM    ModelProperties AS mp 
            WHERE   mp.ModelId = m.Id 
            AND     mp.PropertyId = p.Id
        );

Example on SQL Fiddle