Problem with MySQL query, please help

[Zaggs]

Hi,

I have a problem with this query, seem the be getting a not unique error. Any ideas?

Thanks.

create temporary table uo1 (key (user_id)) select user_id, group_concat(o.occupation SEPARATOR ', ') as occupation from users_occupations as uo inner join occupations as o on o.id = uo.occupation group by user_id;
create temporary table jt1 (key (user_id)) select user_id, group_concat(j.job_type SEPARATOR ', ') as job_type from users_job_types as jt inner join job_types as j on j.id = jt.job_type group by user_id;
create temporary table ks1 (key (user_id)) select user_id, group_concat(k.key_skill SEPARATOR ', ') as key_skill from users_keyskills as ks inner join key_skills as k on k.id = ks.key_skill group by user_id;
select users.title, users.first_name, users.last_name, users.gender, users.city, users.county, users.cv_text, users.signup_date, users_profile.*, uo1.occupation, jt1.job_type, ks1.key_skill from users inner join users_profile on users_profile.userid = users.id left outer join uo1 on uo1.user_id = users.id left outer join jt1 on jt1.user_id = users.id left outer join ks1 on ks1.user_id = users.id inner join ( select user_id from users_job_types where job_type in (1002,1003,1004) group by user_id ) as jts on jts.user_id = users.id inner join ( select user_id from users_sectors where sector in (1040,1021,1024,1027) group by user_id ) as sec on sec.user_id = users.id inner join ( select user_id from users_occupations where occupation in (14,27,45,46) group by user_id ) as occ on occ.user_id = users.id inner join ( select user_id from users_job_types where job_type in (1003) group by user_id ) as jts on jts.user_id = users.id inner join ( select user_id from users_sectors where sector in (1011) group by user_id ) as sec on sec.user_id = users.id inner join ( select user_id from users_occupations where occupation in (51) group by user_id ) as occ on occ.user_id = users.id where users.status='1' and users.signup_date > DATE_SUB(NOW(), INTERVAL 24 HOUR) group by users.id

ERROR:

#1066 - Not unique table/alias: 'jts'

[Zaggs]

I forgot to mention that this sql query is being executed within a loop, which I believe is why this is happening. I tried dropping the temporary table after but it still gives an error. Any ideas how to resolve it?

[swampBoogie]

You have this

(select user_id 
          from users_job_types 
         where job_type in (1002,1003,1004) 
         group 
            by user_id) as jts

and also this

join (select user_id 
          from users_job_types 
         where job_type in (1003) 
         group by user_id) as jts

You need to have unique correlation name for each derived table. There are more occurrances of the same error in your query.

[r937]

I forgot to mention that this sql query is being executed within a loop, which I believe is why this is happening.

the fact that you're running this in a loop is very, very scary

your temporary tables could/should be written as subqueries in a manner similar to the way you've got those other subqueries

the "not unique alias" error has nothing to do with the loop and everything to do with the fact that you've created two of those subqueries with the same name

in fact, you've repeated this mistake again elsewhere... see if you can spot the errors more easily when the query is properly formatted

hint: you should adopt some formatting, friend

select users.title
     , users.first_name
     , users.last_name
     , users.gender
     , users.city
     , users.county
     , users.cv_text
     , users.signup_date
     , users_profile.*
     , uo1.occupation
     , jt1.job_type
     , ks1.key_skill 
  from users 
inner 
  join users_profile 
    on users_profile.userid = users.id 
left outer 
  join uo1 
    on uo1.user_id = users.id 
left outer 
  join jt1 
    on jt1.user_id = users.id 
left outer 
  join ks1 
    on ks1.user_id = users.id 
inner 
  join ( select user_id 
           from users_job_types 
          where job_type in (1002,1003,1004) 
         group 
             by user_id ) as jts 
    on jts.user_id = users.id 
inner 
  join ( select user_id 
           from users_sectors 
          where sector in (1040,1021,1024,1027) 
         group 
             by user_id ) as sec 
    on sec.user_id = users.id 
inner 
  join ( select user_id 
           from users_occupations 
          where occupation in (14,27,45,46) 
         group 
             by user_id ) as occ 
    on occ.user_id = users.id 
inner 
  join ( select user_id 
           from users_job_types 
          where job_type in (1003) 
         group 
             by user_id ) as jts 
    on jts.user_id = users.id 
inner 
  join ( select user_id 
           from users_sectors 
          where sector in (1011) 
         group 
             by user_id ) as sec 
    on sec.user_id = users.id 
inner 
  join ( select user_id 
           from users_occupations 
          where occupation in (51) 
         group 
             by user_id ) as occ 
    on occ.user_id = users.id 
 where users.status='1' 
   and users.signup_date > DATE_SUB(NOW(), INTERVAL 24 HOUR) 
group 
    by users.id