SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with MySQL query, please help

    Hi,

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

    Thanks.

    Code:
    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:
    Code:
    #1066 - Not unique table/alias: 'jts'

  2. #2
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    You have this

    Code:
    (select user_id 
              from users_job_types 
             where job_type in (1002,1003,1004) 
             group 
                by user_id) as jts
    and also this

    Code:
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Zaggs View Post
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •