Mantis Bug Tracker - Forums

Code: Select all

#
# les gestionnaires des projets par user (ID). 
#
# a1 project_user_list coté user
# a2 project_user_list coté projet
#
SELECT 
a1.user_id,
a1.project_id,
mantis_project_table.name,
a2.user_id,
mantis_user_table.username,
mantis_user_table.email
FROM
`mantis_project_user_list_table` AS a1,
`mantis_project_user_list_table` AS a2
INNER JOIN 
`mantis_user_table` ON mantis_user_table.id = a2.user_id
INNER JOIN
`mantis_project_table` ON mantis_project_table.id = a2.project_id
WHERE 
a1.user_id IN ( SELECT id FROM `mantis_user_table`) 
AND
(a1.project_id = a2.project_id AND
a2.access_level = '70')
ORDER BY a1.user_id, a1.project_id ASC ;

Code: Select all

WHERE 
a1.user_id IN ( SELECT id FROM `mantis_user_table`) 

Code: Select all

SELECT
  u.username as user,
  p.name as project_name,
  uM.username as manager

FROM mantis_project_user_list_table as pul
JOIN mantis_user_table as u
  ON pul.user_id=u.id
JOIN mantis_project_table as p
  ON p.id=pul.project_id
JOIN mantis_project_user_list_table as pulM
  ON pulM.project_id=p.id AND pulM.access_level=70
JOIN mantis_user_table as uM
  ON uM.id=pulM.user_id
WHERE pul.access_level<70