php - MySQL query matching 1 or more properties from a subtable

Question

I am using PHP as script language and want to query some tables as below:

CREATE TABLE `objects` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

INSERT INTO `objects` (`id`, `name`) VALUES
    (1, 'Object 1'),
    (2, 'Object 2');

CREATE TABLE `properties` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

INSERT INTO `properties` (`id`, `name`) VALUES
    (1, 'property1'),
    (2, 'property2'),
    (3, 'property3');

CREATE TABLE `lookup` (
    `objectId` INT(11) NULL,
    `propertyId` INT(11) NULL,
    UNIQUE INDEX `objectId_propertyId` (`objectId`, `propertyId`) USING BTREE,
    INDEX `objectId` (`objectId`) USING BTREE,
    INDEX `propertyId` (`propertyId`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

INSERT INTO `lookup` (`objectId`, `propertyId`) VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 1),
    (2, 2),
    (2, 3);

Now I have a multiple selection form (HTML checkboxes), where a user can select one or multiple properties (from the properties tabgle) and search for suitable objects having those selected properties. For one property (e.g. property with id = 2) it is easy as I can use:

SELECT name
  FROM objects
 WHERE id IN (SELECT objectId FROM lookup WHERE propertyId = 2) 

But how can I get objects having multiple propertyIds?

Many thanks for your help!

Answer 1

等待大神答复