CODEWITHSUNDEEP
databasesubquerywildcardsql-likedb-browser-sqlite
InvalidObjects
InvalidObjects

Reputation: 27

SQL LIKE + wildcard operator only returns results with first value

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

Answers (1)

Matt
Matt

Reputation: 115

You can try using WHERE state_code IN (... this will search for all the records in your inner sql query

Upvotes: 0

Related Questions