SQL query questions

[John Lester]

The goal ... find how many users used "man" in their biography field in their profile. Then to change their usergroupid (I can do that much once I understand why the following SELECT query is wrong).

I've been messing around with this query ... but I'm still not familiar enough with JOIN statements to understand why it keeps throwing a syntax error.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN user AS user ON (userfield.userid = user.userid) AND usergroupid = 2 A' at line 2

Query

SELECT * FROM userfield WHERE field1 = 'man' 
LEFT JOIN user AS user ON (userfield.userid = user.userid)
AND usergroupid = 2
AND posts = 0

There is no "user" or "usergroupid" column in the userfield table ... only userid hence my reasoning to join the user table.

I'm using phpmyadmin and have the delimiter ; in the delimiter box hence it not being in the query above.

[squidsk]

Originally Posted by John Lester

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN user AS user ON (userfield.userid = user.userid) AND usergroupid = 2 A' at line 2

Query

SELECT * FROM userfield WHERE field1 = 'man' 
LEFT JOIN user AS user ON (userfield.userid = user.userid)
AND usergroupid = 2
AND posts = 0

There is no "user" or "usergroupid" column in the userfield table ... only userid hence my reasoning to join the user table.

You've got the query in the wrong order, the where clause has to come after all the tables are joined.

SELECT * FROM userfield 
LEFT JOIN user AS user ON (userfield.userid = user.userid)
WHERE field1 = 'man' 
AND usergroupid = 2
AND posts = 0

[John Lester]

Thank you Squid

It makes sense now that I see it done properly