Question 63674 in PhpMyAdmin Question posted in
The official documentation can be found here.

MySQL SELECT INNER JOIN mistaken – Phpmyadmin

I am trying to retrieve the ‘Roles’ sets of a given users.id on a query with INNER JOIN combined with a WHERE condition. But things go 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 returns no data. (Tested on a phpmyadmin SQL board.)

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search