Mariadb: inner join of two queries

Question

Mariadb: inner join of two queries

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I have a table that relates reflections to categories to which it belongs.

idReflexion | idCategoria
---------------------------------
      1     |       2
      1     |       3
      1     |       5
      2     |       3
      2     |       5
      3     |       2
      3     |       3

I want you to show me all those reflections that are in categories a, b, c ... n

For example, if I ask you to show me the reflections that belong to categories 3 and 5, you should show me the 1 and 2. If I want to show me those that belong to categories 2 and 3, you should show me the 1 and the 3.

I have been using INNER JOIN in a thousand and one ways, but nothing ... I always get an error.

mysql mariadb

asked by Javi 21.08.2017 в 13:22

source

2 answers

Problem when doing an echo with data brought from a database Create a txt file on Android in a public way? [closed]

score 2 · Answer 1

If you want to show all the reflections that have 1 or n categories you can look for it in the following way:

SELECT * FROM reflexiones
 INNER JOIN tabla_relacion ON reflexiones.idReflexion = tabla_relacion.idReflexion 
 INNER JOIN tabla_relacion ON tabla_relacion.idCategoria = categorias.idCategoria 
  WHERE idCategoria in (SELECT idCategoria FROM categorias WHERE idCategoria = 3  
  OR idCategoria = 5)

even using a simple inner join should work

SELECT * FROM reflexiones
     INNER JOIN tabla_relacion ON reflexiones.idReflexion = tabla_relacion.idReflexion 
WHERE tabla_relacion.idCategoria = 3 OR tabla_relacion.idCategoria = 5

score 0 · Answer 2

Subject resolved!

Example to obtain the reflections that are at the same time in category 18 and in category 19.

SELECT DISTINCT A1.idReflexion FROM esp_reflexioncategoria AS A1 
INNER JOIN esp_reflexioncategoria AS A2 ON (A1.idReflexion = A2.idReflexion AND A2.idCategoria = 19)
WHERE A1.idCategoria = 18