Solved

CF - SQL query giving multipart identifier error

Posted on 2015-02-10
12
113 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
 
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

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 …
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…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now