I have a database in which I have, among others, 3 tables. The first contains user data, the second contains the relationship between a university major and its subjects, and the last contains the subjects themselves.
Total would have something like this:
mysql> describe users;
+------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| email | varchar(255) | NO | UNI | NULL | |
| password | varchar(255) | NO | | NULL | |
| id_uni | int(11) | NO | MUL | NULL | |
| id_car | int(11) | NO | MUL | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | |
+------------+------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)
mysql> describe unicars;
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| id_uni | int(11) | NO | MUL | NULL | |
| id_car | int(11) | NO | MUL | NULL | |
| id_asig | int(11) | NO | MUL | NULL | |
| cuatrimestre | int(11) | NO | | NULL | |
| ano | int(11) | NO | | NULL | |
+--------------+---------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> describe asignaturas;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| asignatura | varchar(200) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
The content of these tables is as follows:
mysql> select * from users;
+----+---------+---------+----------+--------+--------+---------------------+
| id | name | email | password | id_uni | id_car | created_at |
+----+---------+---------+----------+--------+--------+---------------------+
| 1 | k1k4ss0 | jaja@gg | password | 1 | 1 | 2019-01-20 22:53:49 |
+----+---------+---------+----------+--------+--------+---------------------+
1 row in set (0.00 sec)
mysql> select * from unicars;
+----+--------+--------+---------+--------------+-----+
| id | id_uni | id_car | id_asig | cuatrimestre | ano |
+----+--------+--------+---------+--------------+-----+
| 1 | 1 | 1 | 1 | 1 | 2 |
| 2 | 1 | 1 | 2 | 1 | 2 |
| 3 | 1 | 1 | 3 | 1 | 2 |
| 4 | 1 | 1 | 4 | 1 | 2 |
| 5 | 1 | 1 | 5 | 1 | 2 |
| 6 | 1 | 1 | 6 | 2 | 2 |
| 7 | 1 | 1 | 7 | 2 | 2 |
| 8 | 1 | 1 | 8 | 2 | 2 |
| 9 | 1 | 1 | 9 | 2 | 2 |
| 10 | 1 | 1 | 10 | 2 | 2 |
+----+--------+--------+---------+--------------+-----+
10 rows in set (0.00 sec)
mysql> select * from asignaturas;
+----+---------------------------------------------+
| id | asignatura |
+----+---------------------------------------------+
| 1 | Sistemas Operativos |
| 2 | Ingles Tecnico |
| 3 | Estructura de Computadores |
| 4 | Estadistica |
| 5 | Computabilidad y Algoritmia |
| 6 | Redes y Sistemas Distribuidos |
| 7 | Codigo Deontologico y Aspectos Legales |
| 8 | Algoritmos y Estructuras de Datos Avanzadas |
| 9 | Administracion de Sistemas |
| 10 | Fundamentos de Ingenieria del Software |
+----+---------------------------------------------+
10 rows in set (0.00 sec)
mysql> select * from unis;
+----+--------------------------+
| id | universidad |
+----+--------------------------+
| 1 | Universidad de la Laguna |
+----+--------------------------+
1 row in set (0.00 sec)
The issue is that I can't find a way to make a query that shows me for a user all the subjects of his career and his university.
I have tried the following query:
select name,universidad,carrera,asignatura,cuatrimestre
from users natural join unicars natural join asignaturas natural join
carreras natural join unis order by cuatrimestre,asignatura asc;
but it gives me the following result:
+-----------+--------------------------+------------------------+---------------------+--------------+
| name | universidad | carrera | asignatura | cuatrimestre |
+---------+--------------------------+------------------------+---------------------+--------------+
| k1k4ss0 | Universidad de la Laguna | Ingenieria Informatica | Sistemas Operativos | 1 |
+---------+--------------------------+------------------------+---------------------+--------------+
1 row in set (0.00 sec)
What I want is something similar to this:
+---------------------------------------------+--------------+
| asignatura | cuatrimestre |
+---------------------------------------------+--------------+
| Computabilidad y Algoritmia | 1 |
| Estadistica | 1 |
| Estructura de Computadores | 1 |
| Ingles Tecnico | 1 |
| Sistemas Operativos | 1 |
| Administracion de Sistemas | 2 |
| Algoritmos y Estructuras de Datos Avanzadas | 2 |
| Codigo Deontologico y Aspectos Legales | 2 |
| Fundamentos de Ingenieria del Software | 2 |
| Redes y Sistemas Distribuidos | 2 |
+---------------------------------------------+--------------+
10 rows in set (0.00 sec)
With the difference that it shows the name of the user.
You must do one of
JOINto bring the data and you can put an alias to the tables so that the query is shorter and more readable:I don't see the name of the university anywhere.