Obtain registration with two matches in the same relationship table

The join s are used when you want to get results from several tables and work with the data as if it were a single table.

In this case you want to get a content (that is in two categories), for what I would do:

SELECT *
FROM contenidos
WHERE
  contenidos.id IN (
    SELECT idContenido
    FROM contenido_x_categorias
    WHERE contenido_x_categorias.idCategoria = $idCategoria1 
  )
  AND
  contenidos.id IN (
    SELECT idContenido
    FROM contenido_x_categorias
    WHERE contenido_x_categorias.idCategoria = $idCategoria2 
  )

It is possible that this SQL statement can be optimized. If you do not have many changes in the tables, you can search the product ID listings by category in PHP, and thus avoid those two extra queries.

Enzo, if in your result the information you want to show is in the contenido table, or simply know which ones have two occurrences, you can achieve what you are looking for with the following query:

SELECT  
    con.contenido_id, con.contenido_titulo
FROM contenido_20180116 con 
INNER JOIN contenido_categoria_20180116 cc1 ON con.contenido_id=cc1.contenido_id
INNER JOIN categoria_20180116 cat ON cc1.categoria_id=cat.categoria_id
GROUP BY (con.contenido_id)
HAVING COUNT(con.contenido_id)=2
ORDER BY con.contenido_id;

• You make a single JOIN between contenido and contenido_categoria
• Groups by contenido_id
• You use HAVING to count the records that have two matches. By modifying the value =2 you can apply other filters, also admitting evaluations of the type greater than or equal to , less than or equal to , etc.
• You could add a WHERE filter if you needed it.

Proof of concept

VIEW DEMO IN REXTESTER

Code

CREATE TABLE IF NOT EXISTS contenido_20180116 
(
    contenido_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    contenido_titulo VARCHAR(70),
    contenido_descripcion VARCHAR(70)

)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS categoria_20180116 
(
    categoria_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    categoria_titulo VARCHAR(70)

)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS contenido_categoria_20180116 (
    contenido_id INT, 
    categoria_id INT, 
    UNIQUE KEY (contenido_id, categoria_id),
    FOREIGN KEY (contenido_id) REFERENCES contenido_20180116(contenido_id)
    ON DELETE CASCADE,
    FOREIGN KEY (categoria_id) REFERENCES categoria_20180116(categoria_id)
    ON DELETE CASCADE
)ENGINE=INNODB;


INSERT INTO contenido_20180116 (contenido_titulo, contenido_descripcion)
    VALUES 
    ('Titulo1', 'Descripción1'),
    ('Titulo2', 'Descripción2'),
    ('Titulo3', 'Descripción3'),
    ('Titulo4', 'Descripción4'),
    ('Titulo5', 'Descripción5')
;

INSERT INTO categoria_20180116 (categoria_titulo)
    VALUES 
    ('Cat-Titulo1'),
    ('Cat-Titulo2'),
    ('Cat-Titulo3')
;

INSERT INTO contenido_categoria_20180116 (contenido_id,categoria_id)
    VALUES 
    (1,1),
    (1,3),
    (2,1),
    (2,2),
    (3,1),
    (4,2),
    (5,1)

;

SELECT  
    con.contenido_id, con.contenido_titulo
FROM contenido_20180116 con 
INNER JOIN contenido_categoria_20180116 cc1 ON con.contenido_id=cc1.contenido_id
INNER JOIN categoria_20180116 cat ON cc1.categoria_id=cat.categoria_id
GROUP BY (con.contenido_id)
HAVING COUNT(con.contenido_id)=2
ORDER BY con.contenido_id;

Result

contenido_id    contenido_titulo
1               Titulo1
2               Titulo2