Solved

CF - SQL query giving multipart identifier error

Posted on 2015-02-10
12
114 Views
Last Modified: 2015-02-12
I am selecting a group of records and need to not select the related record from another table.  I am getting the mulit-part identifier error.

Any help is appreciated

SELECT     nominations.Team1, nominations.Team2, nominations.Team3, nominations.Team4, nominations.Team5, nominations.Team6, nominations.Team7,
                      nominations.Team8, nominations.Team9, nominations.Team10, nominations.Team11, nominations.Team12, nominations.Team13, nominations.Team14,
                      nominations.Team15, nominations.astreason, employee_data.ID, employee_data.last_name AS VoterLastName, employee_data.first_name AS VoterFirstName,
                      Main_Department.main_dept AS ASTNAME, nominations.astname AS astid, nominations.voted, nominations.voterid, nominations.recordid AS Expr1
FROM         nominations INNER JOIN
                      employee_data ON nominations.voterid = employee_data.ID INNER JOIN
                      Main_Department ON nominations.astname = Main_Department.ID CROSS JOIN
                      leadervotes
WHERE     (NOT EXISTS
                          (SELECT     1 AS Expr1
                            FROM          leadervotes AS leadervotes_1
                            WHERE      (leadervotes.voteid = n.recordid) AND (leaderid = #SESSIONID#)))
error.jpg
0
Comment
Question by:JohnMac328
  • 6
  • 4
  • 2
12 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40601096
You're using an alias 'n' in your where clause
WHERE     (NOT EXISTS
                          (SELECT     1 AS Expr1
                            FROM          leadervotes AS leadervotes_1
                            WHERE      (leadervotes.voteid = n.recordid) AND (leaderid = #SESSIONID#)))


That might be a typo or an alias that you have used elsewhere but not defined in this query. Should it be "nominations.recordid"?
0
 

Author Comment

by:JohnMac328
ID: 40601107
Very strange - I twice tried to change it to nominations and it crashes SQL
0
 

Author Comment

by:JohnMac328
ID: 40601126
The third time it worked - I want to do some more testing to make sure it is working correctly
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 18

Expert Comment

by:Simon
ID: 40601133
I can't see why you need the cross join in your query. You're not returning any records from the leadervotes table. You can still use it in the subquery in your WHERE clause.
0
 

Author Comment

by:JohnMac328
ID: 40601140
I have to display records that are not in the leadervotes table - records (people) that have already been voted on.
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40601224
OK, I think you could also use this as an alternative to your current where clause:
WHERE  RECORDID NOT IN (SELECT voteid FROM LeaderVotes where leaderid = #SESSIONID#)

Open in new window

0
 

Author Closing Comment

by:JohnMac328
ID: 40601256
That also works and is a little more to the point.

Thanks!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40602466
The original error is simply because you had a reference to alias "n" that did not exist in the querymulti-part identifer = "n" and changing
leadervotes.voteid = n.recordid
to
leadervotes.voteid = nominations.recordid

should have repaired that error.

However that use of CROSS JOIN wasn't required as Simon pointed out
(and that CROSS JOIN would be bad for performance)

While the chosen solution is sound I'd not want you to feel that EXISTS isn't worth using. IN () can have poor performance IF there are very large sets being evaluated, whereas EXISTS can be better in such circumstances.

This should work too I think:
FROM nominations
      INNER JOIN employee_data ON nominations.voterid = employee_data.ID
      INNER JOIN Main_Department ON nominations.astname = Main_Department.ID
WHERE NOT EXISTS (
                  SELECT NULL
                  FROM leadervotes
                  WHERE leadervotes.voteid = nominations.recordid
                        AND leadervotes.leaderid = #SESSIONID#
                 )

Open in new window

0
 

Author Comment

by:JohnMac328
ID: 40603049
I agree - I have read up on it and several articles mention that EXISTS is a preferred method.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40603394
Also agree :)
Just note that the difference in performance is negligible (in my experience) on the most record-sets and, for me, I can read it more quickly when reviewing code than the EXISTS construct.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40604701
as we are all agreed, but still using IN () it seems, let me say this

My preference is to use IN() when the contents is finite, if not:

I avoid IN () like the plague IF the contents of the "in list" would be a potentially unlimited subquery. in other words if I believe the in list could reach very large numbers I try not to use that approach.
   e.g. financial transactions

If on the other had I know from the design that the in list contents is in reasonable bounds then that's fine
   e.g. a lookup table

like most of SQL "it depends"
0
 

Author Comment

by:JohnMac328
ID: 40605463
Good point
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question