MySQL SELECT INNER JOIN mistaken-CodePudding

I try to retreive the 'Roles' sets of given users.id on a query with INNER JOIN clause combined with a WHERE condition. But things goes wrong.

My database has four tables set like that:

t_users : id, username, userpass,...
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 returns no data. (tested on phpmyadmin sql board).

So please, help me.

Edit:
Sorry, there was errors on my query tables names. I've rewrited them but nothing change.
These are my datas set into my tables:

    INSERT INTO `t_users` (`id`, `username`, `password`) VALUES
    (1, 'foo', 'pass1'),
    (2, 'bar', 'pass2');
    
    INSERT INTO `t_role` (`id`, `libelle`, `statut`) VALUES
    (1, 'Operator', 1),
    (2, 'User', 1),
    (5, 'Administrator', 1);
    
    INSERT INTO `t_type_role` (`id`, `libelle`, `statut`) VALUES
    (1, 'Executif', 1),
    (2, 'home', 1),
    (3, 'System', 1);
    
    INSERT INTO `t_action` (`id`, `id_user`, `id_role`, `id_type_role`) VALUES
    (1, 1, 5, 3),
    (2, 2, 5, 3);

CodePudding user response：

as Akina has already mentioned in the comment section, there is no "libelle" value in the table "role" which equals 'Système' as you has mentioned it above. That is the reason why you do not get any output. Fix it to 'System' in the MySQL database and try it out again.

CodePudding user response：

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

Don't put single quotes on integers, 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