meow meow - 4 months ago 12
MySQL Question

Php sql query working fine but as i use two condition in same join not working

Here is my query worked well but want to add anothercondition in it but it giving error Not unique table/alias: 'users'

Here is my code

$query = 'select group_concat(DISTINCT evaluation_section. section_name) as allowed_sections from evaluation_section
join section_permissions on (section_permissions.section_id = evaluation_section.section_id )
join users on ( users.user_id = section_permissions.user_id )
join users on ( users.user_id = section_permissions.assigned_for )
where users.user_id = '.$user['user_id'];


want select name from users table also and then use this condition join users on ( users.user_id = section_permissions.assigned_for )

please help me

This is my edited text
this is my full working query
$q = 'select group_concat(DISTINCT evaluation_section. section_name) as allowed_sections from evaluation_section
join section_permissions on (section_permissions.section_id = evaluation_section.section_id )
join users on ( users.user_id = section_permissions.user_id )
where users.user_id = '.$user['user_id'];

but i want it to select name from users table and then match it with users.user_id = section_permissions.assigned_for so that it get the name of assigned_for id name
as before group_concat i use users.name then its working but it display the name of users.user_id = section_permissions.user_id but i want to display name of er_id )
where users.user_id = '.$user['user_id'];

but i want it to select name from users table and then match it with users.user_id = section_permissions.assigned_for
in same query because in two its not working

Answer

Try doing it this way:

$query = 'select group_concat(DISTINCT evaluation_section. section_name) as   allowed_sections from evaluation_section 
        join section_permissions on (section_permissions.section_id = evaluation_section.section_id )
        join users u1 on ( u1.user_id = section_permissions.user_id )
        join users u2 on ( u2.user_id = section_permissions.assigned_for )
        where  u1.user_id = '.$user['user_id'];  

Since you are joining "users" table twice, you will have to provide unique alias name to both of them.

Comments