how to select state name from states within nested select

Forum Moderators: open

Message Too Old, No Replies

how to select state name from states within nested select

mysql, nested query, nested select

sukebe

6:11 pm on Aug 5, 2010 (gmt 0)

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.

Please help,
thanks so much!

rocknbil

5:53 pm on Aug 6, 2010 (gmt 0)

Did you try this?

SELECT users.fname, users.lname, users.email, users.state FROM users, states where states.stid = 'users.state') and users.company = 'soapland' ;

If you use PHP, your array will still be $row['fname'], etc.

sukebe

4:46 pm on Aug 9, 2010 (gmt 0)

I tried your query but it returned a zero result. I then modified it so that the select had states.short_name instead of users.state, but that didn't help. Also there is a parens after 'users.state'. I wasn't sure, so I tried it without and with an open parens at states.stid =.

basically, within the users table, the users.state is a number that corresponds with states.stid. so SELECT short_name FROM states WHERE stid = '5' will result in 'CA'. I'm trying to get that 'CA' in the query I'm trying, instead of the '5'.

Thanks!

sukebe

4:50 pm on Aug 9, 2010 (gmt 0)

ok, I got it:
SELECT users.fname, users.lname, users.email, states.short_name FROM users, states where users.company = 'soapland' and states.stid = users.state

sukebe

5:05 pm on Aug 9, 2010 (gmt 0)

wait, now I'm stuck again.
The query I'm doing has several ANDs for each company.

So, I tried this, as a test:
SELECT users.company, users.fname, users.lname, states.short_name, users.email FROM users, states WHERE (users.company = 'pamchess' and states.stid = users.state) AND (users.company = 'hess' and states.stid = users.state);

however, this returns a zero result.

Can you help?

Thanks!

sukebe

5:17 pm on Aug 9, 2010 (gmt 0)

duh, here it is:
SELECT users.company, users.fname, users.lname, users.address1, users.address2, users.city, states.short_name AS 'state', users.zip, users.email FROM users, states where (users.company = 'pamchess' and states.stid = users.state) OR (users.company = 'hess' and states.stid = users.state)

rocknbil

2:21 am on Aug 12, 2010 (gmt 0)

Glad you got it, and was away . . . yeah I'm prone to typos. It's a disease I think . . .