postgresql7.4
select distinct u.first_names || ' ' || u.last_name || ' (' || u.email || ')' as name, u.user_id
from cc_users u
where lower(coalesce(u.first_names || ' ', '') ||
coalesce(u.last_name || ' ', '') ||
u.email || ' ' ||
coalesce(u.screen_name, '')) like lower('%'||:value||'%')
AND u.user_id NOT IN (
SELECT user_id
FROM cc_users u, lrc_groups g, group_member_map m
WHERE u.user_id = m.member_id
AND m.group_id = g.group_id
AND g.magic_name = 'instructors'
)
ORDER by name
select distinct u.first_names || ' ' || u.last_name || ' (' || u.email || ')' as name, u.user_id
from cc_users u
where lower(coalesce(u.first_names || ' ', '') ||
coalesce(u.last_name || ' ', '') ||
u.email || ' ' ||
coalesce(u.screen_name, '')) like lower('%'||:value||'%')
AND u.user_id IN (
SELECT user_id
FROM cc_users u, lrc_groups g, group_member_map m
WHERE u.user_id = m.member_id
AND m.group_id = g.group_id
AND g.magic_name = 'instructors'
)
ORDER by name
SELECT distinct u.last_name || ', ' || u.first_names || ' (' || u.email || ')' AS name,
u.user_id
FROM cc_users u
WHERE u.user_id NOT IN (
SELECT user_id
FROM cc_users u, lrc_groups g, group_member_map m
WHERE u.user_id = m.member_id
AND m.group_id = g.group_id
AND g.magic_name = 'instructors'
)
SELECT distinct u.last_name || ', ' || u.first_names || ' (' || u.email || ')' AS name,
u.user_id
FROM cc_users u
WHERE u.user_id IN (
SELECT user_id
FROM cc_users u, lrc_groups g, group_member_map m
WHERE u.user_id = m.member_id
AND m.group_id = g.group_id
AND g.magic_name = 'instructors'
)
SELECT group_id FROM lrc_groups WHERE magic_name = 'instructors'
LIMIT 1
SELECT t.name, t.template_id
FROM lrc_template t
WHERE t.template_id NOT IN (select template_id from lrc_lab_template_map where lab_id = :lab_id)