Welcome to WebmasterWorld Guest from 184.73.112.180

Forum Moderators: open

how to select state name from states within nested select

mysql, nested query, nested select

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

5+ Year Member



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!
5:53 pm on Aug 6, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
4:46 pm on Aug 9, 2010 (gmt 0)

5+ Year Member



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!
4:50 pm on Aug 9, 2010 (gmt 0)

5+ Year Member



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
5:05 pm on Aug 9, 2010 (gmt 0)

5+ Year Member



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!
5:17 pm on Aug 9, 2010 (gmt 0)

5+ Year Member



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)
2:21 am on Aug 12, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month