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
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?
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 [COLOR="#FF0000"]( select user_id
from users_job_types
where job_type in (1002,1003,1004)
group
by user_id ) as jts[/COLOR]
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 [COLOR="#FF0000"]( select user_id
from users_job_types
where job_type in (1003)
group
by user_id ) as jts[/COLOR]
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