Mysql – How to make multiple right joins in one single command for different child tables

mariadbMySQL

On the followin image you see 3 tables: A, B , C.
A: Is the table that holds the foreign keys from B and C

enter image description here

enter image description here

If I do a right join between A and B on that foreign key, it works, meaning I get the data from column fooB. Same between A and C with fooC.

But If I put both right joins under the same command it fails with null

+-------------+---------------+
| fooB        | fooC          |
+-------------+---------------+
| NULL        | abc           |
| NULL        | xyz           |

*This is slightly different as the image with the commands posted below, as this comes straight from the real deployment, however cmds below still show that both columns do not show up.

I've tried mixin up left joins, right joins , outer joins all with same result.
I have googled about this, but the way my question is formulated is awkard and the hits I get return the traditional right joins for just one table.

How do I get this to work in one command? CMDS Below:

enter image description here

Same commands as above but in text, as comments are asking for text rather than images:

MariaDB [joinPOC]> select fooB from tableA right join tableB on tableB_idtableB=tableB.idtableB;
+------+
| fooB |
+------+
| b1   |
| b2   |
| b3   |
+------+
3 rows in set (0.001 sec)

MariaDB [joinPOC]> select fooC from tableA right join tableC on tableC_idtableC=tableC.idtableC;
+------+
| fooC |
+------+
| c1   |
| c2   |
| c3   |
+------+
3 rows in set (0.001 sec)

MariaDB [joinPOC]> select fooB from tableA right join tableB on tableB_idtableB=tableB.idtableB right join tableC on tableC_idtableC=tableC.idtableC;
+------+
| fooB |
+------+
| b1   |
| b2   |
| b3   |
+------+

–HERE IS THE ENTIRE SCRIPT–

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema joinPOC
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `joinPOC` ;

-- -----------------------------------------------------
-- Schema joinPOC
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `joinPOC` ;
USE `joinPOC` ;

-- -----------------------------------------------------
-- Table `joinPOC`.`tableB`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `joinPOC`.`tableB` (
  `idtableB` INT NOT NULL,
  `fooB` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`idtableB`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `joinPOC`.`tableC`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `joinPOC`.`tableC` (
  `idtableC` INT NOT NULL,
  `fooC` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`idtableC`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `joinPOC`.`tableA`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `joinPOC`.`tableA` (
  `idtableA` INT NOT NULL,
  `fooA` VARCHAR(10) NOT NULL,
  `tableB_idtableB` INT NOT NULL,
  `tableC_idtableC` INT NOT NULL,
  PRIMARY KEY (`idtableA`),
  INDEX `fk_tableA_tableB_idx` (`tableB_idtableB` ASC),
  INDEX `fk_tableA_tableC1_idx` (`tableC_idtableC` ASC),
  CONSTRAINT `fk_tableA_tableB`
    FOREIGN KEY (`tableB_idtableB`)
    REFERENCES `joinPOC`.`tableB` (`idtableB`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tableA_tableC1`
    FOREIGN KEY (`tableC_idtableC`)
    REFERENCES `joinPOC`.`tableC` (`idtableC`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `joinPOC`.`tableB`
-- -----------------------------------------------------
START TRANSACTION;
USE `joinPOC`;
INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (1, 'b1');
INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (2, 'b2');
INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (3, 'b3');

COMMIT;


-- -----------------------------------------------------
-- Data for table `joinPOC`.`tableC`
-- -----------------------------------------------------
START TRANSACTION;
USE `joinPOC`;
INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (1, 'c1');
INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (2, 'c2');
INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (3, 'c3');

COMMIT;


-- -----------------------------------------------------
-- Data for table `joinPOC`.`tableA`
-- -----------------------------------------------------
START TRANSACTION;
USE `joinPOC`;
INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (1, 'a1', 1, 1);
INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (2, 'a2', 2, 2);
INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (3, 'a3', 3, 3);

COMMIT;

Desired output in a single command:

+-------------+---------------+
| fooB        | fooC          |
+-------------+---------------+
| b1          | c1            |
| b2          | c2            |

Best Answer

What you want is an INNER JOIN.

To exclude rows, you can use the normal WHERE clause or do it in the ON clause

Schema (MySQL v8.0)


Query #1

SELECT 
    b.fooB, c.fooC
FROM
    tableA a
        INNER JOIN
    tableB b ON a.tableB_idtableB = b.idtableB
        INNER JOIN
    tableC c ON a.tableC_idtableC = c.idtableC;

| fooB | fooC |
| ---- | ---- |
| b1   | c1   |
| b2   | c2   |
| b3   | c3   |

View on DB Fiddle