Several tables in a log with JOIN SQL query

Hi, I am using the following structure to test, relationships are not validated because that is not the goal, I leave the script so you can see that it works.

1-Test script.

-- ----------------------------
-- Table structure for fichamaterial
-- ----------------------------
DROP TABLE IF EXISTS 'fichamaterial';
CREATE TABLE 'fichamaterial' (
  'idreferencia' int(11) NOT NULL AUTO_INCREMENT,
  'categoria' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'marca' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY ('idreferencia')
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of fichamaterial
-- ----------------------------
INSERT INTO 'fichamaterial' VALUES ('1', 'impresora', 'HP');
INSERT INTO 'fichamaterial' VALUES ('2', 'ordenador', 'DELL');
INSERT INTO 'fichamaterial' VALUES ('3', 'monitor', 'Samsung');

-- ----------------------------
-- Table structure for impresoras
-- ----------------------------
DROP TABLE IF EXISTS 'impresoras';
CREATE TABLE 'impresoras' (
  'idreferencia' int(11) NOT NULL,
  'tipol' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'consumible' varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of impresoras
-- ----------------------------
INSERT INTO 'impresoras' VALUES ('1', 'Laser', 'Toner');

-- ----------------------------
-- Table structure for monitores
-- ----------------------------
DROP TABLE IF EXISTS 'monitores';
CREATE TABLE 'monitores' (
  'idreferencia' int(11) NOT NULL,
  'tipom' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'tamano' varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of monitores
-- ----------------------------
INSERT INTO 'monitores' VALUES ('3', 'LED', '21 plg');

-- ----------------------------
-- Table structure for ordenadores
-- ----------------------------
DROP TABLE IF EXISTS 'ordenadores';
CREATE TABLE 'ordenadores' (
  'idreferencia' int(11) NOT NULL,
  'placa' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'procesador' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'ram' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'disco' varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of ordenadores
-- ----------------------------
INSERT INTO 'ordenadores' VALUES ('2', 'Asrock', 'Intel I3', '4G', '250 Gb');

Then we have the query, Of a somewhat complex level but I will explain it in detail.

SELECT
FM.categoria,
FM.marca,
/*En el caso de cada categoria renombramos su campo para que todos salgan bajo un mismo nombre en este caso (P)*/
CASE 
    WHEN(FM.categoria='monitor') THEN MON.tipom
    WHEN(FM.categoria='impresora') THEN IMP.tipol
    WHEN(FM.categoria='ordenador') THEN ORD.placa
END as p,
/*Aplica lo mismo que el anterior para (P2)*/
CASE 
    WHEN(FM.categoria='monitor') THEN MON.tamano
    WHEN(FM.categoria='impresora') THEN IMP.consumible
    WHEN(FM.categoria='ordenador') THEN ORD.procesador
END as p2,

/*En el caso de monitores e impresoras no tienen los otros campos,asi que devolvemos una cadena vacia y ene l caso del ordenado el campo de la ram*/
CASE 
    WHEN(FM.categoria='monitor') THEN ''
    WHEN(FM.categoria='impresora') THEN ''
    WHEN(FM.categoria='ordenador') THEN ORD.ram
END as r,
/*Aplica lo mismo del caso anterior ahora  devolvemos el campo de disco*/
CASE 
    WHEN(FM.categoria='monitor') THEN ''
    WHEN(FM.categoria='impresora') THEN ''
    WHEN(FM.categoria='ordenador') THEN ORD.disco
END as d


FROM
fichamaterial AS FM
/*La sentencia LEF OUTER JOIN Busca el registro, si lo encuentra lo devuelve y si no toma valor vacio, En este caso Condicionamos el JOIN de la siguiente forma
 1- Si la categoria del equipo es monitor, Entonces el JOIN SERA entre fichamaterial y monitores, En caso negativo se devuelve NULL esto signifca que no hará join
 2- Si la categoria del equipo es impresora, Entonces el JOIN SERA entre fichamaterial e impresoras, En caso negativo se devuelve NULL esto signifca que no hará join
 3- Si la categoria del equipo es ordenador, Entonces el JOIN SERA entre fichamaterial y ordenadores, En caso negativo se devuelve NULL esto signifca que no hará join
*/
LEFT OUTER JOIN monitores AS MON ON FM.idreferencia=(CASE WHEN FM.categoria='monitor' THEN MON.idreferencia ELSE NULL END) 
LEFT OUTER JOIN impresoras AS IMP ON FM.idreferencia=(CASE WHEN FM.categoria='impresora' THEN IMP.idreferencia ELSE NULL END) 
LEFT OUTER JOIN ordenadores AS ORD ON FM.idreferencia=(CASE WHEN FM.categoria='ordenador' THEN ORD.idreferencia ELSE NULL END) 

The final result is the following, I hope it helps you (I thought the challenge was interesting, so as you will see I reconstructed the whole scene), of course you can also add more left outer join if you have other categories.

Since you are studying, I think you remember that the relationship you pose is called IS-A (a table that contains general data of a classification and a table for each classification)

I think what you are looking for is a left-join , which is related to the table that you indicate, and leaves empty (null) those fields that are not found. I suppose (because you have not said) that all the elements of the material record are of those 3 types, and that all the elements of the material tab are in some of the tables.

Based on the structure that achilles presents, I have created an example with this query:

select f. *, i.tipol, i.consumable, m.tipom, m.tamano, o.placa, o.processor, o.ram, o.disco from fichamaterial as f left join computers as o on f.idreference = o.idreference left join printers as i on f.idreference = i.referencing left join monitors as m on f.idreference = m.idreference ;

With this, I get a table so that:

+--------------+-----------+---------+-------+------------+-------+--------+----------+------------+------+--------+
| idreferencia | categoria | marca   | tipol | consumible | tipom | tamano | placa    | procesador | ram  | disco  |
+--------------+-----------+---------+-------+------------+-------+--------+----------+------------+------+--------+
|            2 | ordenador | DELL    | NULL  | NULL       | NULL  | NULL   | Asrock   | Intel I3   | 4G   | 250 Gb |
|            4 | ordenador | ASUS    | NULL  | NULL       | NULL  | NULL   | Gigabyte | Intel I7   | 32G  | 1Tb    |
|            1 | impresora | HP      | Laser | Toner      | NULL  | NULL   | NULL     | NULL       | NULL | NULL   |
|            3 | monitor   | Samsung | NULL  | NULL       | LED   | 21 plg | NULL     | NULL       | NULL | NULL   |
+--------------+-----------+---------+-------+------------+-------+--------+----------+------------+------+--------+

(I added the ASUS computer so that there was more than one element of a type)

Now, you can go through the table, and if the element is of a category, show some fields or others.

Look to see if it helps you.