More Left Join query help RSS

• • Reply
  

  StrangerMike

  Contributor

  2707 Points

  1728 Posts

  More Left Join query help

  Nov 27, 2012 02:22 PM|LINK

  Hello, I am trying to pull two almost identical query together with a left join, getting an invalid identifier error.

  Can you help?

  Select t1.story,t1.combo,t1.print_only,t1.web_only,t1.New_accounts,t2.combo
  from (
  SELECT activeaccounts.usernumber, duser.STORY_USER, namefirst_user || ' ' || duser.namelast_user AS story,
    combo,  print_only,  web_only,  (combo + print_only + web_only) AS Total, new_accounts
  FROM activeaccounts,  duser
  WHERE period           = 2
  AND pyear              = 2013
  AND duser.usernumber  = activeaccounts.usernumber
  AND currentrecord_flag = 'TRUE'
  AND duser.usernumber IN
    (SELECT DISTINCT usernumber FROM duser WHERE salesteam_user = 'Team1')
  ORDER BY story_user) t1
  
  Left Join
  
  (SELECT activeaccounts.usernumber, duser.STORY_USER, namefirst_user || ' ' || duser.namelast_user AS story,
    combo,  print_only,  web_only,  (combo + print_only + web_only) AS Total, new_accounts
  FROM activeaccounts,  duser
  WHERE period           = 2
  AND pyear              = 2012
  AND duser.usernumber  = activeaccounts.usernumber
  AND currentrecord_flag = 'TRUE'
  AND duser.usernumber IN
    (SELECT DISTINCT usernumber FROM duser WHERE salesteam_user = 'Team1')
  ORDER BY story_user) t2
  On t1.activeaccounts.usernumber = t2.activeaccounts.usernumber

  Thanks
  • Edited by StrangerMike on Nov 27, 2012 02:24 PM
  • Edited by StrangerMike on Nov 27, 2012 03:22 PM
  • Edited by StrangerMike on Nov 27, 2012 03:22 PM
• • Reply
  

  grundebar

  Contributor

  4515 Points

  726 Posts

  Re: More Left Join query help

  Nov 27, 2012 02:25 PM|LINK

  You have over qualified the columns in your join clause. It should be this:

  On t1.usernumber = t2.usernumber

  "What I hear, I forget; What I see, I remember; What I do, I understand." --Confucius
  
  Remeber to Mark as Answer if this post helped you.
  • Marked as answer by StrangerMike on Nov 27, 2012 03:23 PM
• • Reply
  

  StrangerMike

  Contributor

  2707 Points

  1728 Posts

  Re: More Left Join query help

  Nov 27, 2012 03:23 PM|LINK

  wow, that worked! Thanks

  Thanks