How to build a query from a column centric table and a row centric table?

Question

I want to build a query that combines data from two tables.

test_1 table:

+----+---------+
| id | field_A |
+----+---------+
|  1 |       1 |
|  2 |       1 |
|  3 |       2 |
|  4 |       2 |
+----+---------+

test_2 table:

+----+---------+---------+---------+
| id | field_A | field_B | field_C |
+----+---------+---------+---------+
|  1 |       1 |       1 | baz     |
|  2 |       1 |       2 | zoo     |
|  3 |       2 |       1 | bin     |
|  4 |       2 |       2 | won     |
+----+---------+---------+---------+

field_A is a common field between the two tables and I have a bridge(?) table (test_3) that sits in the middle:

+----+------+
| id | desc |
+----+------+
|  1 | foo  |
|  2 | bar  |
+----+------+

The output of the query is as follows:

+----+---------+------------------+------------------+
| id | field_A | test_2_field_B_1 | test_2_field_B_2 |
+----+---------+------------------+------------------+
|  1 |       1 | baz              | zoo              |
|  2 |       1 | baz              | zoo              |
|  3 |       2 | bin              | won              |
|  4 |       2 | bin              | won              |
+----+---------+------------------+------------------+

Where:

• id and field_A are replicated from test_1
• test_2_field_B_1 is the value where test_1.field_A = test_2.field_A and test_2.field_B = 1
• test_2_field_B_2 is the value where test_1.field_A = test_2.field_A and test_2.field_B = 2

In essence I need to unpivot the data in test_2 which is row centric and then combine it with the column centric test_1.

I've tried following the post here and got as far as:

SELECT
    t1.id_,
    t1.field_A,
    (SELECT field_C FROM test_2 WHERE field_A = 1 AND field_A = t1.field_A) AS test_2_field_B_1,
    (SELECT field_C FROM test_2 WHERE field_A = 2 AND field_A = t1.field_A) AS test_2_field_B_2
FROM test_1 AS t1
GROUP BY t1.id_

However, all I get returned is OK in the Output box on Workbench.

I'd really like to get a query based solution (rather than a view) as eventually I aim to convert this into Python using SQLAlchemy.

Here's the SQL to create the three tables:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test_1`
--

DROP TABLE IF EXISTS `test_1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_1` (
  `id_` int NOT NULL,
  `field_A` int DEFAULT NULL,
  PRIMARY KEY (`id_`),
  KEY `fk1_idx` (`field_A`),
  CONSTRAINT `fk1` FOREIGN KEY (`field_A`) REFERENCES `test_3` (`id_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test_1`
--

LOCK TABLES `test_1` WRITE;
/*!40000 ALTER TABLE `test_1` DISABLE KEYS */;
INSERT INTO `test_1` VALUES (1,1),(2,1),(3,2),(4,2);
/*!40000 ALTER TABLE `test_1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `test_2`
--

DROP TABLE IF EXISTS `test_2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_2` (
  `id_` int NOT NULL,
  `field_A` int DEFAULT NULL,
  `field_B` int DEFAULT NULL,
  `field_C` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id_`),
  KEY `fk2_idx` (`field_A`),
  CONSTRAINT `fk2` FOREIGN KEY (`field_A`) REFERENCES `test_3` (`id_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test_2`
--

LOCK TABLES `test_2` WRITE;
/*!40000 ALTER TABLE `test_2` DISABLE KEYS */;
INSERT INTO `test_2` VALUES (1,1,1,'baz'),(2,1,2,'zoo'),(3,2,1,'bin'),(4,2,2,'won');
/*!40000 ALTER TABLE `test_2` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `test_3`
--

DROP TABLE IF EXISTS `test_3`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_3` (
  `id_` int NOT NULL,
  `desc` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test_3`
--

LOCK TABLES `test_3` WRITE;
/*!40000 ALTER TABLE `test_3` DISABLE KEYS */;
INSERT INTO `test_3` VALUES (1,'foo'),(2,'bar');
/*!40000 ALTER TABLE `test_3` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Answer 1

I think you want:

select t1.id, t1.field_a,
    (select t2.field_c from test_2 t2 where t2.field_a = t1.field_a and t2.field_b = 1) test_2_field_b_1,
    (select t2.field_c from test_2 t2 where t2.field_a = t1.field_a and t2.field_b = 2) test_2_field_b_2
from test_1 t1

You can implement the same logic with a single join and conditional aggregation:

select t1.id, t1.field_a,
    max(case when t2.field_b = 1 then t2.field_c end) test_2_field_b_1,
    max(case when t2.field_b = 2 then t2.field_c end) test_2_field_b_2
from test_1 t1
inner join test_2 t2 on t2.field_a = t1.field_a
group by t1.id, t1.field_a