Error with SQL, Help?

[The-Ensemble]

I have a hack that is meant to show a certain usergroups's members and it keeps giving SQL errors, first it was a table didn't exist then something else after that, I can't get it to work.

The Original SQL Query

PHP Code:

$users = $db->query_read("
    SELECT $locationfieldselect user.*, user.options
    FROM " . TABLE_PREFIX . "user AS user
    LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid = user.userid)
    WHERE " . TABLE_PREFIX . "user.usergroupid = 12
"); 


I'm using VB 3.6.7 if that helps at all.

Thanks in advance.

[Paul M]

You need to post the actual error message - "something else" isn't much use.

[The-Ensemble]

Ok well here is the first one that says the table doesn't exist.

Database error in vBulletin 3.6.7:

Invalid SQL:

SELECT user.*, user.options
FROM vb_user AS user
LEFT JOIN vb_userfield AS userfield ON(userfield.userid = user.userid)
WHERE vb_user.usergroupid = 12;

MySQL Error : Unknown table 'vb_user' in where clause
Error Number : 1109
Date : Thursday, October 4th 2007 @ 04:14:07 PM
Script : /showgroups.php?do=contributors
Referrer :
IP Address : ----------
Username : ---------
Classname : vb_database

[Paul M]

It should be user.usergroup = 12

[The-Ensemble]

Whoops that was the error message from after I tried editing it to get it to work,
here the error message from when Its using the original query.

Database error in vBulletin 3.6.7:

Invalid SQL:

SELECT user.*, user.options
FROM vb_user AS user
LEFT JOIN vb_userfield AS userfield ON(userfield.userid = user.userid)
WHERE vb_user.usergroupid = 8;

MySQL Error : Unknown table 'vb_user' in where clause
Error Number : 1109

[Paul M]

Nothing has changed - the answer is the same ;

The WHERE statement should be user.usergroupid, not vb_user.usergroupid.

[The-Ensemble]

thats the table_prefix thingy *points to query in first*

[Paul M]

Yes, I know what it is, that's the point - you need to remove it from the where line, it's not needed.

[The-Ensemble]

Oh
It works now! Thank you!