homepage Welcome to WebmasterWorld Guest from 54.82.1.136
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




msg:4182649
 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




msg:4183230
 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




msg:4184302
 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




msg:4184303
 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




msg:4184311
 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




msg:4184316
 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




msg:4185658
 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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved