Doubt in the relationship of Mysql Workbench tables

Question

Hello, I have a small problem creating a database.

My goal is to create a table to mark the attendance of people in the classes of the courses in which they are enrolled.

The presecas table must contain the person, course and class id. My goal is to insert a Dropdown in the attendance table where only the class Ids that belong to the courses in which the person is enrolled appear.

I have tried several calls and in the insertion, in attendance table, always appear Ids of all classes, I need only appear the Ids of the classes that belong to the courses in which the person is enrolled.

This image should help you better understand what I want to do. Thanks in advance ;)

Answer 1

My suggestion is that you remove the entire table presence. You already have a table called pessoa_has_curso - I suggest you create a table called pessoa_has_aula, with FOREIGN KEYS of pessoa and of aula.

I don’t have the Mysql Workbench to demonstrate, but created a reduced version of your DB.

Table curso:

CREATE TABLE curso (
curso_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
curso_nome VARCHAR(45),
PRIMARY KEY (curso_id)
);

Table aula:

CREATE TABLE aula (
aula_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
aula_nome VARCHAR(45),
curso_id INT UNSIGNED NOT NULL,
PRIMARY KEY (aula_id),
FOREIGN KEY (curso_id) REFERENCES curso (curso_id)
);

Table pessoa:

CREATE TABLE pessoa (
pessoa_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
pessoa_nome VARCHAR(45),
PRIMARY KEY (pessoa_id)
);

Table pessoa_has_curso:

CREATE TABLE pessoa_has_curso (
pessoa_id INT UNSIGNED NOT NULL,
curso_id INT UNSIGNED NOT NULL,
FOREIGN KEY (pessoa_id) REFERENCES pessoa (pessoa_id),
FOREIGN KEY (curso_id) REFERENCES curso (curso_id)
);

And now, the new table, pessoa_has_aula:

CREATE TABLE pessoa_has_aula (
pessoa_id INT UNSIGNED NOT NULL,
aula_id INT UNSIGNED NOT NULL,
FOREIGN KEY (pessoa_id) REFERENCES pessoa (pessoa_id),
FOREIGN KEY (aula_id) REFERENCES aula (aula_id)
);

I made several insertions of people, courses and classes - but I will not put here for brevity. The placement of a pessoa in a curso, happens like this:

INSERT INTO pessoa_has_curso (pessoa_id, curso_id) VALUES (3, 1);

I also put pessoa in aula:

INSERT INTO pessoa_has_aula (pessoa_id, aula_id) VALUES (1, 6);

Now, by making the right inquiries, I believe you get them all the information you need.

For example, list items from pessoa who are also in curso:

SELECT pessoa.pessoa_nome AS Pessoas, curso.curso_nome AS Cursos
FROM pessoa_has_curso
INNER JOIN pessoa
ON pessoa_has_curso.pessoa_id = pessoa.pessoa_id
INNER JOIN curso
ON pessoa_has_curso.curso_id = curso.curso_id;

Upshot:

Listing now items from pessoa who are also in aula:

SELECT pessoa.pessoa_nome AS Pessoas, aula.aula_nome as Aulas
FROM pessoa_has_aula
INNER JOIN pessoa
ON pessoa_has_aula.pessoa_id = pessoa.pessoa_id
INNER JOIN aula
ON pessoa_has_aula.aula_id = aula.aula_id;

Upshot:

Now, to answer your question, you said you need IDs of aula in which pessoa is participating.

As now we have a table pessoa_has_aula, this survey is using only it, without involving the table curso:

SELECT aula_id AS AulaID
FROM pessoa_has_aula
INNER JOIN pessoa
ON pessoa_has_aula.pessoa_id = pessoa.pessoa_id
WHERE pessoa.pessoa_nome = 'Azurelle';

Upshot:

Take a look at the SQL Fiddle.