I have three related tables users, groups and group_members. The users table contains users information and groups table contains the group name and group owner id (ie, users_id). Table group_members contains the members of each groups (user_id and group_id). I tried to load the users details of each group but the result is not correct. My tables are,
users
id firstname lastname email
1 anu mm [email protected]
2 manu mohan [email protected]
3 cinu ab [email protected]
4 vinu mj [email protected]
5 vijesh cc [email protected]
6 admin admin [email protected]
groups
id user_id name
1 1 group1
2 2 group2
3 2 group3
4 3 group4
group_members
id user_id group_id
1 1 1
2 2 2
3 2 3
4 3 4
5 2 3
7 1 2
8 1 3
9 3 1
But when I tried the bellow query,
select users.firstname,users.lastname,users.email,groups.name as groupname from users inner join groups on groups.user_id = users.id inner join group_members on group_members.group_id = groups.id where groups.id=3 AND users.firstname LIKE 'cin%' OR users.lastname LIKE 'cin%' OR users.email LIKE 'cin%'
getting the result (which is not expected)
firstname lastname email groupname
cinu ab [email protected] group4
In this case I used the where condition WHERE groups.id =3 but the result takes the group4 (groups.id is 4). Please suggest me a solution.
your have to use () arround your or statements:
where groups.id=3 AND (users.firstname LIKE 'cin%' OR users.lastname LIKE 'cin%' OR users.email LIKE 'cin%')
Because AND has a higher priority you query looks like:
where (groups.id=3 AND users.firstname LIKE 'cin%') OR users.lastname LIKE 'cin%' OR users.email LIKE 'cin%'
And that is not what you want.