How to make a purchase matrix in DQL or MYSQL

Question

How to make a purchase matrix in DQL or MYSQL

Navigation

#1 by (1 votes)

1

I would like to create a matrix with two tables in sql in the following way:

I have a table that contains the data:

another table:

id | person | id form
00 | 00 | 02
01 | 00 | 03
02 | 00 | 04
03 | 01 | 04
04 | 02 | 02
05 | 03 | 01
06 | 03 | 04

and that the result of the query is:

NOTE : If to do it with MySQL a lot of resource is consumed that is not necessary and it is better to do a basic query and create the matrix with the other language, I use PHP AND JQUERY , sending everything from php to jquery by < strong> JSON

 create table formularios(
id int NOT NULL AUTO_INCREMENT,
nombre varchar(20),
PRIMARY KEY (id)
);

create table personas(
id int NOT NULL AUTO_INCREMENT,
nombre varchar(20),
PRIMARY KEY (id)
);

create table relacion(
id int NOT NULL AUTO_INCREMENT,
persona int,
formulario int,
PRIMARY KEY (id),
FOREIGN KEY (persona) REFERENCES personas(id),
FOREIGN KEY (formulario) REFERENCES formularios(id),
);

mysql javascript php dql

asked by ANDRES FERNANDO MARTINEZ VALEN 09.07.2018 в 07:52

source

1 answer

How can I call the show methodData of the child class in my main? Bearing in mind that there are builders Get form variables when submit and pass them to a php

score 1 · Answer 1

If what you want is to obtain the information directly in a SQL query then what you need is a CROSS JOIN for each form and person to return a record. The LEFT JOIN will relate the first two tables giving NULL in their fields if the relationship does not exist:

SELECT
  f.id fid,
  f.nombre formulario,
  p.id pid,
  p.nombre persona,
  IF(r.persona IS NULL, '', 'X') marca
FROM formularios f
CROSS JOIN personas p
LEFT JOIN relacion r
  ON r.formulario = f.id AND r.persona = p.id
ORDER BY
  f.id, p.id ASC
;

You can try it online at this link

Result:

fid formulario    pid  persona   marca
 0  formulario 0   0   persona.0   
 0  formulario 0   1   persona.1   
 0  formulario 0   2   persona.2   
 0  formulario 0   3   persona.3   
 0  formulario 0   4   persona.4   
 1  formulario 1   0   persona.0   
 1  formulario 1   1   persona.1   
 1  formulario 1   2   persona.2   
 1  formulario 1   3   persona.3   X
 1  formulario 1   4   persona.4   
 2  formulario 2   0   persona.0   X
 2  formulario 2   1   persona.1   
 2  formulario 2   2   persona.2   X
 2  formulario 2   3   persona.3   
 2  formulario 2   4   persona.4   
 3  formulario 3   0   persona.0   X
 3  formulario 3   1   persona.1   
 3  formulario 3   2   persona.2   
 3  formulario 3   3   persona.3   
 3  formulario 3   4   persona.4   
 4  formulario 4   0   persona.0   X
 4  formulario 4   1   persona.1   X
 4  formulario 4   2   persona.2   
 4  formulario 4   3   persona.3   X
 4  formulario 4   4   persona.4