Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CF - SQL query giving multipart identifier error

Posted on 2015-02-10
12
Medium Priority
?
120 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 2000 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

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

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

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 post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
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…

688 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