Solved

CF - SQL query giving multipart identifier error

Posted on 2015-02-10
12
118 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 49

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 49

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

621 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