Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: The column prefix '*' does not match with a table name or alias name used...

    Hi. I wish there was an SQL Beginner's Forum because I am very new to SQL and this forum. I searched for this problem and found a few threads on this error, but either A) It wasn't related to my code or B) It might have been related and explained the problem perfectly, but I don't understand it. So, this is my code:
    Code:
    SELECT 
    	togredesign.LP_Class.Days, 
    	togredesign.LP_Class.Date, 
    	togredesign.LP_Class.Time, 
    	togredesign.LP_Class.Fee,  
    	togredesign.LP_Course.CourseName, 
    	togredesign.LP_Course.CourseDescription, 
    	togredesign.LP_Course.CourseNotesItalics, 
    	togredesign.LP_Course.CourseNotesBold, 
    	togredesign.LP_Age.Age, 
    	togredesign.LP_Location.Location, 
    	togredesign.LP_Instructor.Instructor, 
    	togredesign.LP_Instructor.InstructorNotes
    		
    FROM togredesign.LP_Class
    		INNER JOIN togredesign.LP_Course ON togredesign.LP_Course.CourseID = togredesign.LP_Class.CourseID
    		INNER JOIN togredesign.LP_Location ON togredesign.LP_Location.LocationID = togredesign.LP_Class.LocationID
    		INNER JOIN togredesign.LP_Age ON togredesign.LP_Age.AgeID = togredesign.LP_Class.AgeID
    		INNER JOIN togredesign.LP_Instructor ON togredesign.LP_Instructor.InstructorID = togredesign.LP_Class.InstructorID
    		INNER JOIN togredesign.LP_InstructorNotes ON togredesign.LP_InstructorNotesID = togredesign.LP_Class.InstructorNotesID
    WHERE 
    	(togredesign.CourseName LIKE '%#form.searchbar#%') OR
    	(togredesign.CourseDescription LIKE '%#form.searchbar#%') OR
    	(togredesign.LocationID = '#form.locationID#')...
    (there is more but I don't think it's related...)

    And I get this error:
    Code:
    The column prefix 'togredesign' does not match with a table name or alias name used in the query.
    From what I've researched, I think this error is related to referencing a table that is not in the FROM section, but the tables I'm referencing are in the FROM section right? Any ideas? Thanks-

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    take a look at your SELECT clause

    you use three-part naming for the columns there -- databasename.tablename.columname

    take a look at your WHERE clause

    you use two-part naming for the columns there, so it thinks the first part (togredesign) is the tablename

    get it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Posts
    4
    oops! haha, yeah, that was left over from me troubleshooting this. The original code and current code under WHERE is now:

    Code:
    	WHERE 
    		(togredesign.LP_Course.CourseName LIKE '%#form.searchbar#%') OR
    		(togredesign.LP_Course.CourseDescription LIKE '%#form.searchbar#%') OR
    		(togredesign.LP_Location.LocationID = '#form.locationID#')
    But I still get the original error...

    Because I am INNER JOINing the LP_Course to the LP_Class table, can I still reference the LP_Course table in WHERE or would I have to reference the columns in the LP_Course table through the LP_Class table somehow in the WHERE section?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shaftbond View Post
    oops!


    But I still get the original error...
    check your entire query for any two-part names you may have overlooked

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2010
    Posts
    4
    hey, i'm back thanks again for your help r937. I went in and rechecked for two-part names and couldn't find any. Here is the entire cfquery:

    Code:
    	<cfquery name="getAll" datasource="#application.dbdsn#">
    	SELECT 
    		togredesign.LP_Class.Days, 
    		togredesign.LP_Class.Date, 
    		togredesign.LP_Class.Time, 
    		togredesign.LP_Class.Fee,  
    		togredesign.LP_Course.CourseName, 
    		togredesign.LP_Course.CourseDescription, 
    		togredesign.LP_Course.CourseNotesItalics, 
    		togredesign.LP_Course.CourseNotesBold, 
    		togredesign.LP_Age.Age, 
    		togredesign.LP_Location.Location, 
    		togredesign.LP_Instructor.Instructor, 
    		togredesign.LP_Instructor.InstructorNotes
    		
    	FROM togredesign.LP_Class
    			INNER JOIN togredesign.LP_Course ON togredesign.LP_Course.CourseID = togredesign.LP_Class.CourseID
    			INNER JOIN togredesign.LP_Location ON togredesign.LP_Location.LocationID = togredesign.LP_Class.LocationID
    			INNER JOIN togredesign.LP_Age ON togredesign.LP_Age.AgeID = togredesign.LP_Class.AgeID
    			INNER JOIN togredesign.LP_Instructor ON togredesign.LP_Instructor.InstructorID = togredesign.LP_Class.InstructorID
    			INNER JOIN togredesign.LP_InstructorNotes ON togredesign.LP_InstructorNotesID = togredesign.LP_Class.InstructorNotesID
    	WHERE 
    		(togredesign.LP_Course.CourseName LIKE '%#form.searchbar#%') OR
    		(togredesign.LP_Course.CourseDescription LIKE '%#form.searchbar#%') OR
    		(togredesign.LP_Location.LocationID = '#form.locationID#')
        	<cfif FORM.AgeType IS "2">
        		AND togredesign.LP_Class.AgeID IN (1,2,3,4,5,6)
        	</cfif>
        	<cfif FORM.AgeType IS "3">
        		AND togredesign.LP_Class.AgeID IN (1,2,3,4,5,6,7,8,9,10,11)
        	</cfif>
        	<cfif FORM.AgeType IS "4">
        		AND togredesign.LP_Class.AgeID IN (5,6,7,8,9,10,11,12,13)
        	</cfif>
        	<cfif FORM.AgeType IS "5">
        		AND togredesign.LP_Class.AgeID IN (6,8,9,10,11,12,13,14,15,16)
        	</cfif>
        	<cfif FORM.AgeType IS "6">
        		AND togredesign.LP_Class.AgeID IN (14,15,16)
        	</cfif>
    	
    		ORDER BY togredesign.LP_Course.CourseName ASC
    	</cfquery>
    I wrote the AgeType part myself and it was working and then I asked another coder (with more experience) here at work to help with the searchbar part and she added the code on top, but couldn't figure out why we were getting the error..

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shaftbond View Post
    I went in and rechecked for two-part names and couldn't find any.
    take a closer look, pls
    Code:
    	FROM togredesign.LP_Class
    			INNER JOIN togredesign.LP_Course ON togredesign.LP_Course.CourseID = togredesign.LP_Class.CourseID
    			INNER JOIN togredesign.LP_Location ON togredesign.LP_Location.LocationID = togredesign.LP_Class.LocationID
    			INNER JOIN togredesign.LP_Age ON togredesign.LP_Age.AgeID = togredesign.LP_Class.AgeID
    			INNER JOIN togredesign.LP_Instructor ON togredesign.LP_Instructor.InstructorID = togredesign.LP_Class.InstructorID
    			INNER JOIN togredesign.LP_InstructorNotes ON togredesign.LP_InstructorNotesID = togredesign.LP_Class.InstructorNotesID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    also, it might be a bit easier on you if you were to use table aliases

    Code:
    	<cfquery name="getAll" datasource="#application.dbdsn#">
    	SELECT 
    		cls.Days, 
    		cls.Date, 
    		cls.Time, 
    		cls.Fee,  
    		crs.CourseName, 
    		crs.CourseDescription, 
    		crs.CourseNotesItalics, 
    		crs.CourseNotesBold, 
    		age.Age, 
    		loc.Location, 
    		ins.Instructor, 
    		ins.InstructorNotes
    		
    	FROM togredesign.LP_Class AS cls
    			INNER JOIN togredesign.LP_Course AS crs
    ON crs.CourseID = cls.CourseID
    			INNER JOIN togredesign.LP_Location AS loc
    ON loc.LocationID = cls.LocationID
    			INNER JOIN togredesign.LP_Age AS age
    ON age.AgeID = cls.AgeID
    			INNER JOIN togredesign.LP_Instructor AS ins
    ON ins.InstructorID = cls.InstructorID
    			INNER JOIN togredesign.LP_InstructorNotes AS inn
    ON inn.ID = cls.InstructorNotesID
    	WHERE 
       ( -- parentheses around your ORs
    		crs.CourseName LIKE '%#form.searchbar#%' OR
    		crs.CourseDescription LIKE '%#form.searchbar#%' OR
    		loc.LocationID = '#form.locationID#'
       ) -- parentheses around your ORs
        	<cfif FORM.AgeType IS "2">
        		AND cls.AgeID IN (1,2,3,4,5,6)
        	</cfif>
        	<cfif FORM.AgeType IS "3">
        		AND cls.AgeID IN (1,2,3,4,5,6,7,8,9,10,11)
        	</cfif>
        	<cfif FORM.AgeType IS "4">
        		AND cls.AgeID IN (5,6,7,8,9,10,11,12,13)
        	</cfif>
        	<cfif FORM.AgeType IS "5">
        		AND cls.AgeID IN (6,8,9,10,11,12,13,14,15,16)
        	</cfif>
        	<cfif FORM.AgeType IS "6">
        		AND cls.AgeID IN (14,15,16)
        	</cfif>
    	
    		ORDER BY crs.CourseName ASC
    	</cfquery>
    see what the table aliases do? less forest, more trees!!!

    also, you had needless parentheses around each of your three OR conditions, which i removed

    i'm pretty sure you do need the parentheses around all your ORs, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It might have been easier if the tables had been properly named to begin with. Then there would be no need for aliases.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    It might have been easier if the tables had been properly named to begin with. Then there would be no need for aliases.
    dude, pay attention and try to keep up

    without table aliases you have to use three-part names

    which is what we were talking about for the last few posts

    doing away with the table aliases results in way too much forest for those few trees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm...you can also use synonyms, and then you really won't need aliasing
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You know its the "LP_" crap I'm objecting to (as well as the "cls", "crs", "inn" crap...).

    And yeah, a synonym would be much better architecture.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jul 2010
    Posts
    4
    Thanks so much for your help- I'm going to order your book today (I need it )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •