• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1892
  • Last Modified:

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

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
edensandavant
Asked:
edensandavant
  • 2
1 Solution
 
Brendt HessSenior DBACommented:
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
 
edensandavantAuthor Commented:
That was quick!
Thanks for the answer... after 4 advil I can put this issue to rest :)
0
 
Brendt HessSenior DBACommented:
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
 
Wardy_01Commented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now