Re: sql WHERE NOT EXISTS
ALternatively, if you are selecting for a particular User and just want to know if it has a Role, just check to see if the Role array is empty in $data. On Mon, Aug 10, 2009 at 11:42 AM, CobaltShark wrote: > > I am not sure if this is what you are looking for, but wouldn't you > get what your looking for (and avoid the subquery) by WHERE'ing > roles.id IS NULL? Maybe I am misunderstanding what data you are trying > to pull as the "where user_id = 3" in the subquery is a bit confusing > to me, are you running that query for each user_id ? > > On Aug 9, 2:46 pm, "euromark (munich)" > wrote: >> does anybody know if there is a cake function for the WHERE NOT EXISTS >> functionality? >> >> i have "users" who have many "roles" in the join table "role_users" - >> and i want to display the ones not having any roles yet >> >> id user_id role_id >> 1 2 3 >> 2 3 3 >> etc >> (user 1 not in the list yet, so does not have any role) >> >> with mysql itself if would say: >> select * from users left join role_users where not exists (select * >> from role_users where user_id = 3) >> for example >> >> i could do some complex reversing - but was looking for some more >> convinient way to do it :) >> >> thx, mark > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: sql WHERE NOT EXISTS
I am not sure if this is what you are looking for, but wouldn't you get what your looking for (and avoid the subquery) by WHERE'ing roles.id IS NULL? Maybe I am misunderstanding what data you are trying to pull as the "where user_id = 3" in the subquery is a bit confusing to me, are you running that query for each user_id ? On Aug 9, 2:46 pm, "euromark (munich)" wrote: > does anybody know if there is a cake function for the WHERE NOT EXISTS > functionality? > > i have "users" who have many "roles" in the join table "role_users" - > and i want to display the ones not having any roles yet > > id user_id role_id > 1 2 3 > 2 3 3 > etc > (user 1 not in the list yet, so does not have any role) > > with mysql itself if would say: > select * from users left join role_users where not exists (select * > from role_users where user_id = 3) > for example > > i could do some complex reversing - but was looking for some more > convinient way to do it :) > > thx, mark --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
sql WHERE NOT EXISTS
does anybody know if there is a cake function for the WHERE NOT EXISTS functionality? i have "users" who have many "roles" in the join table "role_users" - and i want to display the ones not having any roles yet id user_id role_id 1 2 3 2 3 3 etc (user 1 not in the list yet, so does not have any role) with mysql itself if would say: select * from users left join role_users where not exists (select * from role_users where user_id = 3) for example i could do some complex reversing - but was looking for some more convinient way to do it :) thx, mark --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
