-1

I need to create an outer join using inline sql. I can not figure this out.

select el.emp_number,el.first_name,el.last_name,e.job_desc_abbr as item_description,
el.level_one,el.level_one_firstname,el.level_one_lastname,
el.level_two,
el.level_two_firstname,el.level_two_lastname,
el.level_three,el.level_three_firstname,el.level_three_lastname
 from emp_level el,emp_information_test e
 right join (select empid,job_desc_abbr from emp_information_test) level1 on level1.empid = el.level_one   <--- Error
  where el.emp_number = e.empid
  and  e.deptno <> '999'
  and e.empid in ('547787','253010')

Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "el.level_one" could not be bound.

I am not sure if this is possible. I need help with syntax. Thanks

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
h hardaway
  • 7
  • 1
  • 1
    It seems your query is incomplete. You should have the `SELECT` keyword at the start. – johnny243 Sep 26 '19 at 14:56
  • 2
    You are mixing implicit joins with explicit joins. Don't. Use only explicit joins, or go back in time to the previous millennium, when implicit joins was still considered valid. – Zohar Peled Sep 26 '19 at 15:05
  • Use proper joins? `from emp_level el full outer join emp_information_test e on el.emp_number = e.empid right join (...) level1 on level1.empid=el.level_one ...` I think it should be inner join not full outer join but I'll leave that to you. – avery_larry Sep 26 '19 at 15:06
  • 1
    If you need to make changes to your question - use [Edit]. Please do not post code in comments. – PM 77-1 Sep 26 '19 at 15:08
  • 1
    Aside: It wouldn't hurt to provide the DDL for the tables. – HABO Sep 26 '19 at 15:11
  • CREATE TABLE [dbo].[emp_information]([empid] [varchar](6) NOT NULL,[job_desc_abbr] [varchar](50) NULL) CREATE TABLE [dbo].[emp_level]([EMP_NUMBER] [varchar](50) NULL, [FIRST_NAME] [varchar](50) NULL,[LAST_NAME] [varchar](50) NULL, [LEVEL_ONE] [varchar](50) NULL,[LEVEL_ONE_FIRSTNAME] [varchar](50) NULL,[LEVEL_ONE_LASTNAME] [varchar](50) NULL,[LEVEL_TWO] [varchar](50) NULL,[LEVEL_TWO_FIRSTNAME] [varchar](50) NULL,[LEVEL_TWO_LASTNAME][varchar](50) NULL,[LEVEL_THREE] [varchar](50) NULL,[LEVEL_THREE_FIRSTNAME] [varchar](50) NULL,[LEVEL_THREE_LASTNAME][varchar](50) NULL) – h hardaway Sep 26 '19 at 15:22
  • 2
    Please avoid using the comments to add missing parts of the question. Instead, simply [edit] the question (There's also an edit link directly under the question itself). – Zohar Peled Sep 26 '19 at 15:27
  • Possible duplicate of [The multi-part identifier could not be bound. Sql Server](https://stackoverflow.com/questions/43909093/the-multi-part-identifier-could-not-be-bound-sql-server) – Trenton McKinney Sep 26 '19 at 16:04
  • This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Sep 26 '19 at 18:18
  • Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular-formatted base table initialization. – philipxy Sep 26 '19 at 18:19
  • Possible duplicate of [The multi-part identifier could not be bound](https://stackoverflow.com/questions/7314134/the-multi-part-identifier-could-not-be-bound) – philipxy Sep 26 '19 at 18:45

1 Answers1

0

As I wrote in my comment, your problem is the mix between implicit and explicit joins.
Since explicit joins have been a part of ANSI-SQL for almost 30 years now, there really is no need to use implicit joins anymore, ever.

Here's what should be a working version of your select statement:

SELECT   el.emp_number
        ,el.first_name
        ,el.last_name
        ,e.job_desc_abbr AS item_description
        ,el.level_one
        ,el.level_one_firstname
        ,el.level_one_lastname
        ,el.level_two
        ,el.level_two_firstname
        ,el.level_two_lastname
        ,el.level_three
        ,el.level_three_firstname
        ,el.level_three_lastname
FROM emp_level AS el
JOIN emp_information_test AS e
    ON el.emp_number = e.empid
RIGHT JOIN (
    SELECT   empid
            ,job_desc_abbr 
    FROM emp_information_test
) AS level1 
     ON level1.empid = el.level_one   
WHERE e.deptno <> '999'
AND e.empid IN ('547787','253010')
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121