We help IT Professionals succeed at work.

please show input

honey whitman
honey whitman used Ask the Experts™
on
How to Write a sql query to retrieve the loan number, state and city, customer first name for loans that are in the states of CA,TX,FL,NV,NM but exclude the following cities (Dallas, SanFrancisco, Oakland) and only return loans where customer first name begins with John.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
Without knowing your structure, which you did not provide and assuming it is in a single table....

select loan_number, state, city, first_name
from loans
where state in ('CA','TX','FL','NV','NM')
and city not in ('Dallas', 'SanFrancisco', 'Oakland')
and first_name like 'John%'
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Sounds like homework.  What have you tried?

One assumes Oakland means California.

How are you to handle someone that lives in Oakland Florida?

https://en.wikipedia.org/wiki/Oakland,_Florida
johnsoneSenior Oracle DBA

Commented:
Requirements do not say exclude Oakland, CA.  Requirements say exclude cities called Oakland.

Looks like homework to me too.  That's why I gave basics.  I'm sure there is more than one table involved.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I understand what was provided.  Just suggesting what was provided may or may not be 100% accurate.
awking00Information Technology Specialist

Commented:
Just in case slightwv's possible issue is indicated for the actual requirement -
select loan_number, state, city, first_name
 from loans
 where state in ('CA','TX','FL','NV','NM')
 and first_name like 'John%'
except
select loan_number, state, city, first_name
 where city in ('Dallas', 'SanFrancisco', 'Oakland')