homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

how to select state name from states within nested select
mysql, nested query, nested select

Msg#: 4182647 posted 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!



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

Msg#: 4182647 posted 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.


Msg#: 4182647 posted 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'.



Msg#: 4182647 posted 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


Msg#: 4182647 posted 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?



Msg#: 4182647 posted 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)


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

Msg#: 4182647 posted 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 . . .

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved