Incorrect syntax in sql script

Question

not sure what I am doing wrong here.

SELECT DISTINCT
  managername,
  username,
  UserStatus,
  Usertitle,
  Loginid,
  Server,
  DBName,
  Response,
  busjustify,
  Comments,
  dtaccessnoneed
FROM (SELECT
  username,
  UserStatus,
  Usertitle,
  Loginid,
  Server,
  DBName,
  Response,
  busjustify,
  Comments,
  dtaccessnoneed,
  workbkname
FROM tbldb2midtierresponse
WHERE username IS NOT NULL
AND username <> 'User Name') a
INNER JOIN (SELECT DISTINCT
  managername,
  workbkname
FROM tbldb2midtierresponse
WHERE managername NOT IN ('Mid Tier', 'User Access Management', 'Reporting Manager')
AND managername IS NOT NULL) b

INNER JOIN (SELECT
  username,
  UserStatus,
  Usertitle,
  Loginid,
  Server,
  DBName,
  Response,
  busjustify,
  Comments,
  dtaccessnoneed,
  workbkname
FROM tbldb2midtierresponse
WHERE username IS NOT NULL
AND username <> 'User Name/ID Owner') c
  ON b.workbkname = c.workbkname

Error I am getting is at the last line.

Please help where I am doing wrong is this script?

Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'workbkname'.

Outshined · Accepted Answer · 2016-09-23 23:06:13Z

1

Simplfied:

select * 
from (select ...) a 
inner join  (select ... ) b -- [!] missing on x.attr = b.attr statement 
inner join (select ... ) c on b.workbkname = c.workbkname

You didn't join table b right (no common attribute specified)

answered Sep 23, 2016 at 23:06

Outshined

7607 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Over a year ago

OP is also missing table aliases for the outer select which will cause an ambiguous column error.

Matt Over a year ago

@user300485 OP = Original Post, at least that is what I read it as from all of the comments I am pretty new too

Kannan Kandasamy · Accepted Answer · 2016-09-23 23:08:08Z

0

you need to add

a.workbkname = b.workbkname

select distinct managername, username,UserStatus, Usertitle, Loginid,Server,DBName,Response,busjustify, Comments, dtaccessnoneed
from
(select username,UserStatus, Usertitle, Loginid,Server,DBName,Response,busjustify, Comments, dtaccessnoneed,workbkname from tbldb2midtierresponse 
where username is not null and username <> 'User Name' ) a
inner join 
(select distinct managername ,workbkname from tbldb2midtierresponse where managername not in ('Mid Tier','User Access Management','Reporting Manager') and managername is not null ) b
on a.workbkname = b.workbkname
inner join
(select username,UserStatus, Usertitle, Loginid,Server,DBName,Response,busjustify, Comments, dtaccessnoneed,workbkname from tbldb2midtierresponse where username is not null and username <> 'User Name/ID Owner' ) c
on b.workbkname = c.workbkname

answered Sep 23, 2016 at 23:08

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

1 Comment

Matt Over a year ago

OP is also missing table aliases for the outer select which will cause an ambiguous column error

Matt · Accepted Answer · 2016-09-23 23:20:57Z

So you actually have a couple of issues.

Missing On Condition between table aliases a & b
Missing Table Aliases for what table to pull the columns from it will through an ambiguous column error
and lastly inner joins back to the same table may not be necessary.

In your code it appears that you are getting all users then joining to make sure the workbkname also has a manager that is not in Mid tier... and is not null. Then joining to Get everyone else that works on work book....

I would guess you are actually trying to get all users that are not 'User Name' or 'User Name/ID Owner' that have a managername that is NOT IN ('Mid Tier','User Access Management','Reporting Manager'). If so I would do it as follows:

SELECT DISTINCT
    a.managername
    ,a.username
    ,a.UserStatus
    ,a.Usertitle
    ,a.Loginid
    ,a.Server
    ,a.DBName
    ,a.Response
    ,a.busjustify
    ,a.Comments
    ,a.dtaccessnoneed
FROM
    tbldb2midtierresponse a
    LEFT JOIN tbldb2midtierresponse m
    ON a.workbkname  = m.workbkname
    AND m.managername NOT IN ('Mid Tier','User Access Management','Reporting Manager')
    AND m.managername IS NOT NULL
WHERE
    username IS NOT NULL
    AND username NOT IN ('User Name','User Name/ID Owner')
    AND m.username IS NOT NULL

Collectives™ on Stack Overflow

Incorrect syntax in sql script

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related