Querying both ancestors from a bridging table getting cross join?

Question

Hi I have the following ERM,

• kit table
• component table
• kit_has_substitutes - bridging table

that links a kit to two components, intended to represent the situation where a primary component can be substituted with another component. Here is the data in each:

I'm trying to query a list of all component part numbers and their substitute part numbers for a specific kit. So for example for KIT01:

• COMPONENT2 can be substituted for COMPONENT1
• COMPONENT4 can be substituted for COMPONENT3.

I've got as far as the following SQL, which is giving me too many results some of which are incorrect (I suspect I'm doing a cross-join)

SELECT CMP1.part_number ,CMP2.part_number
FROM component CMP1, component CMP2
WHERE CMP1.pk_component in
    (SELECT KHS1.component_pk_primary  
    FROM kit_has_substitutes KHS1
    WHERE KHS1.kit_pk_kit=( SELECT pk_kit 
                            FROM kit 
                            WHERE kitcode = 'KIT01'))
AND CMP2.pk_component in
    (SELECT KHS1.component_pk_sub  
    FROM kit_has_substitutes KHS1
    WHERE KHS1.kit_pk_kit=( SELECT pk_kit 
                            FROM kit 
                            WHERE kitcode = 'KIT01'))

RESULT                      
COMPONENT1  COMPONENT2
COMPONENT1  COMPONENT4
COMPONENT3  COMPONENT2
COMPONENT3  COMPONENT4

DESIRED RESULT
COMPONENT1  COMPONENT2
COMPONENT3  COMPONENT4

How can I further restrict this query to just return a single row for each pair of primary and sub components? Or is there a better way to do it, am I on the wrong track?

I suspect the solution is simple, but I have not used SQL much since the 1990's so I am rusty incarnate. I'm using MySQL. Here is the DDL and DML for this problem:

DROP TABLE IF EXISTS `component`;
CREATE TABLE `component` (
  `pk_component` int NOT NULL AUTO_INCREMENT,
  `part_number` varchar(20) NOT NULL,
  PRIMARY KEY (`pk_component`),
  UNIQUE KEY `pk_component_UNIQUE` (`pk_component`),
  UNIQUE KEY `code_UNIQUE` (`part_number`)
) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `kit`;
CREATE TABLE `kit` (
  `pk_kit` int NOT NULL AUTO_INCREMENT,
  `kitcode` varchar(20) NOT NULL,
  PRIMARY KEY (`pk_kit`),
  UNIQUE KEY `code_UNIQUE` (`kitcode`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


DROP TABLE IF EXISTS `kit_has_substitutes`;
CREATE TABLE `kit_has_substitutes` (
  `kit_pk_kit` int NOT NULL,
  `component_pk_primary` int NOT NULL,
  `component_pk_sub` int NOT NULL,
  PRIMARY KEY (`kit_pk_kit`,`component_pk_primary`,`component_pk_sub`),
  KEY `fk_kit_has_substitutes_component1_idx` (`component_pk_primary`),
  KEY `fk_kit_has_substitutes_component2_idx` (`component_pk_sub`),
  CONSTRAINT `fk_kit_has_substitutes_component1` FOREIGN KEY (`component_pk_primary`) REFERENCES `component` (`pk_component`),
  CONSTRAINT `fk_kit_has_substitutes_component2` FOREIGN KEY (`component_pk_sub`) REFERENCES `component` (`pk_component`),
  CONSTRAINT `fk_kit_has_substitutes_kit1` FOREIGN KEY (`kit_pk_kit`) REFERENCES `kit` (`pk_kit`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;       

INSERT INTO `component` VALUES (150,'COMPONENT1'),(151,'COMPONENT2'),(152,'COMPONENT3'),(153,'COMPONENT4'),(154,'COMPONENT5');
INSERT INTO `kit` VALUES (31,'KIT01'),(32,'KIT02');
INSERT INTO `kit_has_substitutes` VALUES (31,150,151),(31,152,153);

Answer 1

This is how you could approach the problem.

The input is a kit – so start with the kit table:

SELECT
  *
FROM
  kit
WHERE
  kitcode = 'KIT01'
;

This is the output:

pk_kit	kitcode
31	KIT01

Simple and clear. In order to get the components, first you need to join the bridging table:

SELECT
  *
FROM
  kit
  INNER JOIN kit_has_substitutes AS bridge
    ON kit.pk_kit = bridge.kit_pk_kit
WHERE
  kit.kitcode = 'KIT01'
;

Output becomes:

pk_kit	kitcode	kit_pk_kit	component_pk_primary	component_pk_sub
31	KIT01	31	150	151
31	KIT01	31	152	153

So far so good, but we only got references while we need part numbers instead. Time to join the component table, twice, because you need to resolve both references:

SELECT
  *
FROM
  kit
  INNER JOIN kit_has_substitutes AS bridge  ON kit.pk_kit = bridge.kit_pk_kit
  INNER JOIN component           AS cmp_pri ON bridge.component_pk_primary = cmp_pri.pk_component
  INNER JOIN component           AS cmp_sub ON bridge.component_pk_sub     = cmp_sub.pk_component
WHERE
  kit.kitcode = 'KIT01'
;

Output:

pk_kit	kitcode	kit_pk_kit	component_pk_primary	component_pk_sub	pk_component	part_number	pk_component	part_number
31	KIT01	31	150	151	150	COMPONENT1	151	COMPONENT2
31	KIT01	31	152	153	152	COMPONENT3	153	COMPONENT4

Now we have enough information. I dare say, we even have too much if you only want the part numbers. So the final step is to tweak the SELECT list to specify just what we want:

SELECT
  cmp_pri.part_number AS primary_part
, cmp_sub.part_number AS substitute_part
FROM
  kit
  INNER JOIN kit_has_substitutes AS bridge  ON kit.pk_kit = bridge.kit_pk_kit
  INNER JOIN component           AS cmp_pri ON bridge.component_pk_primary = cmp_pri.pk_component
  INNER JOIN component           AS cmp_sub ON bridge.component_pk_sub     = cmp_sub.pk_component
WHERE
  kit.kitcode = 'KIT01'
;

And we arrive at the expected output:

primary_part	substitute_part
COMPONENT1	COMPONENT2
COMPONENT3	COMPONENT4

A live demo of this solution is available here.