Reputation: 27
What's happening StackOverflow.
I'm using DB Browser for SQLLite to query a database with two tables - people
and states
. people
has the field state_code
and states
has the fields state_abbrev
and state_name
. people.state_code
and states.state_abbrev
both have postal codes for USA states (e.g. AK, AZ, MI, MN, etc.).
I'm trying to search for all records where the state names match a certain text string criteria, using LIKE and wildcards. Here's my code:
SELECT *
FROM people
WHERE state_code = (
SELECT state_abbrev FROM states WHERE state_name LIKE 'mi%');
I want the above code to return all records where the state name begins with "mi" - i.e. Michigan, Minnesota, and Missouri. However, this is only returning records with the first alphabetical state name - Michigan. Same happens with LIKE '%ans%'
- this only returns records from Arkansas, despite the existence of records from Kansas.
What am I doing wrong yall? I've tried using GROUP BY state_name, state_abbrev
within the nested SELECT
to no avail, and I can't seem to find anyone else encountering the same issue.
Upvotes: 0
Views: 161
Reputation: 115
You can try using WHERE state_code IN (...
this will search for all the records in your inner sql query
Upvotes: 0