CODEWITHSUNDEEP
mysqlsql
Talked
Talked

Reputation: 173

how to get data from relational tables (many to many)

enter image description here

I have these three main tables Microdisenos, competencias and resultados.

My problem is this: I want to know what are the resultados that belong to the competencias of a microdisenos.

I know how to do it when relationships are one to many, but in this case it is many to many and I do not know how to handle those intermediate tables.

Thanks for your help.

Upvotes: 0

Views: 77

Answers (2)

meDev
meDev

Reputation: 907

CREATE DATABASE testDB;

USE testDB;


CREATE TABLE microdisenos (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  description VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE competencias (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  modulo VARCHAR(128),
  PRIMARY KEY (id)
);

CREATE TABLE competencia_microdiseno (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  microdiseno_id INTEGER UNSIGNED,
  competencia_id INTEGER UNSIGNED,
  PRIMARY KEY (id),
  FOREIGN KEY (microdiseno_id) REFERENCES microdisenos (id),
  FOREIGN KEY (competencia_id) REFERENCES competencias (id)
);

CREATE TABLE resultados (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  description VARCHAR(256),
  PRIMARY KEY (id)
);

CREATE TABLE competencia_resultado(
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  resultado_id INTEGER UNSIGNED,
  competencia_id INTEGER UNSIGNED,
  PRIMARY KEY (id),
  FOREIGN KEY (resultado_id) REFERENCES resultados (id),
  FOREIGN KEY (competencia_id) REFERENCES competencias (id)
);

INSERT INTO competencias VALUES (0, 'Compentencia AA');
INSERT INTO competencias VALUES (0, 'Compentencia BB');
INSERT INTO competencias VALUES (0, 'Compentencia CC');
INSERT INTO competencias VALUES (0, 'Compentencia DD');
INSERT INTO competencias VALUES (0, 'Compentencia EE');

INSERT INTO microdisenos VALUES (0, 'Microdisenos 101');
INSERT INTO microdisenos VALUES (0, 'Microdisenos 202');
INSERT INTO microdisenos VALUES (0, 'Microdisenos 303');
INSERT INTO microdisenos VALUES (0, 'Microdisenos 404');
INSERT INTO microdisenos VALUES (0, 'Microdisenos 505');

INSERT INTO resultados VALUES (0, 'Resultados 11');
INSERT INTO resultados VALUES (0, 'Resultados 22');
INSERT INTO resultados VALUES (0, 'Resultados 33');
INSERT INTO resultados VALUES (0, 'Resultados 44');
INSERT INTO resultados VALUES (0, 'Resultados 55');

INSERT INTO competencia_microdiseno VALUES(0, 1, 1);
INSERT INTO competencia_microdiseno VALUES(0, 1, 2);
INSERT INTO competencia_microdiseno VALUES(0, 1, 3);
INSERT INTO competencia_microdiseno VALUES(0, 2, 4);
INSERT INTO competencia_microdiseno VALUES(0, 2, 5);
INSERT INTO competencia_microdiseno VALUES(0, 3, 1);
INSERT INTO competencia_microdiseno VALUES(0, 3, 2);
INSERT INTO competencia_microdiseno VALUES(0, 4, 3);
INSERT INTO competencia_microdiseno VALUES(0, 4, 4);
INSERT INTO competencia_microdiseno VALUES(0, 4, 5);
INSERT INTO competencia_microdiseno VALUES(0, 5, 1);
INSERT INTO competencia_microdiseno VALUES(0, 5, 2);
INSERT INTO competencia_microdiseno VALUES(0, 5, 3);


INSERT INTO competencia_resultado VALUES(0, 1, 1);
INSERT INTO competencia_resultado VALUES(0, 2, 2);
INSERT INTO competencia_resultado VALUES(0, 3, 3);
INSERT INTO competencia_resultado VALUES(0, 4, 4);
INSERT INTO competencia_resultado VALUES(0, 5, 5);
INSERT INTO competencia_resultado VALUES(0, 1, 1);
INSERT INTO competencia_resultado VALUES(0, 2, 2);
INSERT INTO competencia_resultado VALUES(0, 3, 3);
INSERT INTO competencia_resultado VALUES(0, 4, 4);
INSERT INTO competencia_resultado VALUES(0, 5, 5);
INSERT INTO competencia_resultado VALUES(0, 1, 1);
INSERT INTO competencia_resultado VALUES(0, 2, 2);
INSERT INTO competencia_resultado VALUES(0, 3, 3);


-- Give me all (unique) Resultados for Compentencias for given Microdisenos  
SELECT DISTINCT r.*
  FROM resultados r, competencia_resultado cr, competencias c, competencia_microdiseno cm, microdisenos m
 WHERE r.id = cr.resultado_id
  AND  c.id = cr.competencia_id
  AND  c.id = cm.competencia_id
  AND  m.id = cm.microdiseno_id
  AND  m.description = "Microdisenos 303";

The result would be:

# id, description
----  ---------------
   1, Resultados 11
   2, Resultados 22

Option 2: Subquery

SELECT DISTINCT r.*
 FROM resultados r
WHERE r.id IN ( SELECT DISTINCT cr.id
                  FROM competencia_resultado cr
                 WHERE cr.competencia_id IN ( 
                            SELECT DISTINCT c.id
                              FROM competencias c
                             WHERE c.id IN (
                                    SELECT DISTINCT cm.competencia_id 
                                      FROM competencia_microdiseno cm, microdisenos m 
                                     WHERE m.id = cm.microdiseno_id
                                       AND  m.description = "Microdisenos 303" )
                              )
                );

Upvotes: 1

tommy
tommy

Reputation: 322

The only way is to join throught all the columns:

SELECT * (or whatever you need)
FROM resultados r
 INNER JOIN competencia_resultado cr
  ON r.id = cr.resultado_id
 INNER JOIN cometencias c
  ON c.id = cr.cometencia_id
 INNER JOIN competencia_microdisendo cm
  ON c.id = cm.competencia_id
 INNER JOIN microdisendos m
  ON m.id = cm.microdisendo_id

And if you want to select just the once that belong to one specific microdesendo add the WHERE clause with m.id

Upvotes: 1

Related Questions