Solved

GRRR!! Column prefix does not match with table name or alias name used in the query, help!!

Posted on 2004-10-25
1,861 Views
Last Modified: 2012-06-27
Hey guys / gals.. I've got this little problem that is just more annoying that anything else and i'm curious to see how I could get it fixed.... any and all help will be appreciated!

SELECT SubUser.firstName as submitted_by_firstname, SubUser.lastName as submitted_by_lastname,
ComplUser.firstName as completed_by_firstname, ComplUser.lastName as completed_by_lastname,
RE.neededBy, RE.submittedBy, RE.spacenum, RE.shoppingCenter, RE.requiredOptions,
RE.phone, RE.description, RE.tenantName, RE.devContact, RE.address, RE.city, RE.state, RE.zip
FROM request R, revision RE
inner join users SubUser ON RE.submittedBy = SubUser.userID
inner join users ComplUser ON R.completedBy = ComplUser.userID
inner join R ON RE.requestID = R.requestID
WHERE RE.revisionID = 63;

When I run the above query I keep getting this error message:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'R' does not match with a table name or alias name used in the query.


I've gone through and double checked that I have used the appropriate alias  everytime I have referred to that specific table in this query... this is really driving me nuts! I've had this happen before and I can't remember why it happens and/or how I fixed it. Just for future concern... why is this happening so I can avoid this? I know it's probably something small!

Thanks!!!

0
Question by:edensandavant
    4 Comments
     
    LVL 32

    Accepted Solution

    by:
    Your WHERE clause was hosed.  I have reformatted the query for clarity, and annotated my changes:

    SELECT SubUser.firstName as submitted_by_firstname,
          SubUser.lastName as submitted_by_lastname,
          ComplUser.firstName as completed_by_firstname,
          ComplUser.lastName as completed_by_lastname,
          RE.neededBy,
          RE.submittedBy,
          RE.spacenum,
          RE.shoppingCenter,
          RE.requiredOptions,
          RE.phone,
          RE.description,
          RE.tenantName,
          RE.devContact,
          RE.address,
          RE.city,
          RE.state,
          RE.zip
    FROM revision RE                                                                        -- Removed request R, from this line.  Join is below
    inner join users SubUser ON RE.submittedBy = SubUser.userID
    inner join request R ON RE.requestID = R.requestID                         -- Added name of table here
    inner join users ComplUser ON R.completedBy = ComplUser.userID   -- reorganized - moved line down
    WHERE RE.revisionID = 63
    0
     

    Author Comment

    by:edensandavant
    That was quick!
    Thanks for the answer... after 4 advil I can put this issue to rest :)
    0
     
    LVL 32

    Expert Comment

    by:bhess1
    Rule of thumb:

    Don't mix the join forms (explicit INNER JOIN vs Table1, table2 WHERE) -- it always ends up causing problems.  I strongly recommend always using the explicit joins, to make the join sequence clearer.
    0
     
    LVL 4

    Expert Comment

    by:Wardy_01
    I'm having the same problem ...
    I get this :
    Msg 107, Level 16, State 2, Line 2
    The column prefix 'tvbc_ch_live.dbo' does not match with a table name or alias name used in the query.

    From this query :
    INSERT INTO tvbc_ch_live.dbo.property (class, uprn, blpu, east, north, rb_propref, post_code, ctax_band, parish, address_status, address_type, ward, street, p_start_no, p_end_no, p_start_suffix, p_end_suffix, p_desc, s_start_no, s_end_no, s_start_suffix, s_end_suffix, s_desc, po_box, display_address, residential, business, addressable, parent, land_use, candidate, abc_exclude, organisation)
    SELECT class, uprn, blpu, east, north, rb_propref, post_code, ctax_band, parish, address_status, address_type, ward, street, p_start_no, p_end_no, p_start_suffix, p_end_suffix, p_desc, s_start_no, s_end_no, s_start_suffix, s_end_suffix, s_desc, po_box, display_address, residential, business, addressable, parent, land_use, candidate, abc_exclude, organisation
    FROM tvbc_ch_test.dbo.property
    WHERE NOT EXISTS (
          SELECT 1 FROM tvbc_ch_live.dbo.property
          WHERE tvbc_ch_live.dbo.surrid = tvbc_ch_test.dbo.property.surrid
    )

    ........

    HELP !!!
    It's something obvious i'm betting :(
    Sorry if I hijacked the thread but i figured this is relevant as it's the same kind of error, what does this error even mean anyway?
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Course: JavaScript Coding - Massive 12-Part Bundle

    Regardless of your programming skill level, you'll go from basics to advanced concepts in a vast array of JavaScript subjects including Sammy.js, Agility.js, Ember.js, Node.js, jQuery, AJAX, Extjs, AngularJS, Knockout.js, and JSON.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    875 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

    10 Experts available now in Live!

    Get 1:1 Help Now