I have a long standard query where one of the field pulled from the table is 'state'. However, and for whatever reason, the 'state' field in users is set up to require an additional query to a 'states' table, where 'users.states' = 'states.stid'. Within 'states', the desired field is 'short_name'.
I tried this, which, of course, didn't work, but might illustrate what I'm trying to get out of this:
SELECT fname, lname, email, state,(SELECT short_name FROM states where stid = 'users.state') FROM `users` WHERE company = 'soapland'
clearly, this doesn't work, primarily because the current entry in the field 'users.state' hasn't been looked up, yet. however, if I try to include that into the nested field it looks like it would spiral into a infinite number of nests.
The reason why I need this to be as concise as possible is because there are nearly 300 ANDs after the initial WHERE in the query to generate the result.
thanks so much!