I have problem joing tables.

one table is user table the other one is user role table and one user
can have multiple roles
I want to find out if there is any users without a specific roles

user table
1. Jone Done
2. Micheal Canue

user role
uid roles_id
1 2
1 3
2 1
2 2
3 1

and i want to find out if there is any user without role 3?

On Feb 18, 8:54*am, cake-learner <sh.koiz... (AT) gmail (DOT) com> wrote:
SELECT
ut.name,
FROM user_table ut
LEFT JOIN user_role ur ON ut.uid = ur.uid AND ur.doles_id = 3
WHERE ur.id IS NULL

On 18-02-11 10:04, Captain Paralytic wrote:
"ur.doles_id = 3 WHERE ur.id IS NULL"...
Above will not find any records...

SELECT
ut.name,
FROM user_table ut
LEFT JOIN user_role ur ON ut.uid = ur.uid AND ur.roles_id = 3

And the first question, which was overlooked:

"I want to find out if there is any users without a specific roles"
SELECT
ut.name,
FROM user_table ut
LEFT JOIN user_role ur ON ut.uid = ur.uid AND ur.roles_id IS NULL


--
Luuk

In article <1oq138-ru4.ln1 (AT) luuk (DOT) invalid.lan>, Luuk <Luuk (AT) invalid (DOT) lan> wrote:
This won't work - it will return all rows from user_table. The IS NULL
test needs to be in a WHERE, not in the join condition:

SELECT
ut.name,
FROM user_table ut
LEFT JOIN user_role ur ON ur.uid=ut.uid
WHERE ur.uid IS NULL;

Since we are only looking for lack of a joined record, I test ur.uid
in the WHERE condition rather than looking at a different column.

This will return all users that do not have any role.

If on the other hand you want all users who do not have a certain role,
such as role 3, you need to add the required role to the join condition,
but still filter the result set for lack of a joined record:

SELECT
ut.name,
FROM user_table ut
LEFT JOIN user_role ur ON ur.uid=ut.uid AND ur.roles_id=3
WHERE ur.uid IS NULL;

This will return al users who do not have role 3, regardless of whether
they possess other roles.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

On Feb 18, 9:29*am, Luuk <L... (AT) invalid (DOT) lan> wrote:
Yes I hit the D instead of the r, but apart from that my query was
correct. Your one will not work as you have the NULL test in the wrong
place.

On 2011-02-18 09:54, cake-learner wrote:
As a complement to other suggestions in this thread you can use an
exists predicate as in:

select ...
from user u
where not exists (
select 1 from user_role ur
where ur.uid = u.uid
and ur.roles_id = 3
);


As a sidenote, a question like "What users have all roles" is quite easy
to express via nested exist predicates:

create table user (
uid int not null primary key,
name varchar(25) not null
) engine=innodb;

create table user_role (
uid int not null,
role_id int not null,
constraint pk_user_role primary key (uid, role_id)
) engine=innodb;

insert into user values (1,'Jone'),(2,'Michael');
insert into user_role values (1,2),(1,3),(2,1),(2,2),(2,3);

-- users where it does not exist a role
-- that the user don't have

select * from user u where not exists (
select 1 from user_role x
where not exists (
select 1 from user_role y
where x.role_id = y.role_id
and y.uid = u.uid
)
);
+-----+---------+
1 row in set (0.00 sec)



/Lennart

On 18-02-11 11:18, Captain Paralytic wrote:
Your query is Ok (apart from that typo), but i think the OP asked two
questions, which distracted me too much, which lead to /me giving the
wrong answer(s)... ;(


--
Luuk

Lennart Jonsson:

Interesting!
I would have counted the total number of roles (R), and then selected
the users for which their number of roles is less than R.


--
Erick

On 2011-02-18 12:44, Erick T. Barkhuis wrote:
It is the other obvious way to do relational division. Something like:

select u.* from user u
join (
select uid, count(role_id)
from user_role
group by uid
having count(role_id) = (
select count(distinct role_id)
from user_role
)
) x on u.uid = x.uid;

+-----+---------+


An interesting thing is what happens when there are no roles in
user_role, what users have all roles then?

delete from user_role;

select u.*
from user u
join (
select uid, count(role_id)
from user_role
group by uid
having count(role_id) = (
select count(distinct role_id)
from user_role
)
) x on u.uid = x.uid;
Empty set (0.00 sec)


select * from user u
where not exists (
select 1 from user_role x
where not exists (
select 1 from user_role y
where x.role_id = y.role_id
and y.uid = u.uid
)
);
+-----+---------+


No user or all users? Dependent on how we define the correct answer, to
the question under that circomstance, one of the queries will be
preferable over the other. I would say that in most situations this
difference can be ignored :-)


/Lennart