Wrong use of MySQL SELECT INNER JOIN

Question

I'm trying to retrieve the set of "roles" for a given users.id using an INNER JOIN query combined with a WHERE condition. But something went wrong.

My database has four tables:

t_users : id, username, userpass, status, ...
t_action: id, id_user, id_role, id_type_role, ...
t_role: id, libelle, status
t_type_role: id, libelle, status

My query:

SELECT U.id AS ID, R.libelle AS ROLE, T.libelle AS TYPE
FROM t_user U
JOIN t_action A ON A.id_user = U.id
JOIN t_type_role T ON T.id = A.id_type_role
JOIN t_role R ON R.id = A.id_role
WHERE A.id_user = '1' AND R.libelle = 'System'

But this query did not return any data. (Tested on phpmyadmin SQL board.)

Answer 1

As Akina already mentioned in the comments section, there is no "libelle" value in the table "role", it is equal to "Système", as you mentioned above. That's why you don't get any output. Fix it to "System" in the MySQL database and try again.

Answer 2

use:

SELECT u.id AS id,
       r.libelle AS role,
       t.libelle AS type
FROM users u
JOIN action a ON a.id_user = u.id
JOIN type_role t ON t.id = a.id_type_role
JOIN role r ON r.id = a.id_role
WHERE a.id_user =1
AND t.libelle = 'System';

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=20570938deb2bec281d5070dd28bf19d 一个>

Do not put single quotes around the integer, change WHERE a.id_user ='1' to WHERE a.id_user = 1.

libelle = 'system' is in the type_role table, not in the role table