Solved

CF - SQL query giving multipart identifier error

Posted on 2015-02-10
12
115 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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