0
Hello, Check out this query:
SELECT `permission`.`visible`, `menu`.*
FROM `tb_permission` `permission`
LEFT JOIN `tb_menu` as `menu` ON `menu`.`id` = `permission`.`menu_id`
WHERE `permission`.`visible` = 1
AND `permission`.`type` = 0
OR `permission`.`type` = 1
AND `permission`.`role_id` = '1'
ORDER BY `menu`.`id` ASC
Now look at the result:
It should not only come results, whose field visible is equal to 1 ?
2
The problem is in the conditions of WHERE in your query. When to match conditions AND with OR it is always better to use parentheses to define well what you want. See this Thread in the OS that talks about the priority of conditions in a query: https://stackoverflow.com/a/4872213/7380348
In case is going the results of type = 1 also. Not showing in the results you posted to be sure but I believe that’s it.
To solve this, do it: ... WHERE visible = 1 AND (type = 0 OR type = 1) AND role_id = 1...
1
SELECT `permission`.`visible`, `menu`.*
FROM `tb_permission` `permission`
LEFT JOIN `tb_menu` as `menu` ON `menu`.`id` = `permission`.`menu_id`
WHERE `permission`.`visible` = 1
AND `permission`.`type`in (0,1)
AND `permission`.`role_id` = '1'
ORDER BY `menu`.`id` ASC
forehead like this, must be falling in this OR
1
As already mentioned, the OR of your query must be generating the problems, always try to use the OR condition between relatives:
SELECT `permission`.`visible`, `menu`.*
FROM `tb_permission` `permission`
LEFT JOIN `tb_menu` as `menu` ON `menu`.`id` = `permission`.`menu_id`
WHERE `permission`.`visible` = 1
AND ( `permission`.`type` = 0 OR `permission`.`type` = 1 )
AND `permission`.`role_id` = '1'
ORDER BY `menu`.`id` ASC
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
"It should not only come results, whose Visible field is equal to 1", see if the
orof your query is not bringing unwanted results– Ricardo Pontual
@Ricardopunctual, yes, it is! If I remove, I can see the correct results, I withdrew the
or, and traded for!= 2and it worked. Thank you– Wagner Fillio