How do I search and select names that start with certain letters?-CodePudding

SELECT FirstName, LastName, Email
FROM PATIENT
WHERE [LastName] LIKE 'K%' OR 'Ma%' AND [FirstName] LIKE 'K%' OR 'Ma%';

I need to display patients' first name, last name, and email whose last or first name starts with 'K' or 'Ma.'

This is my code and I am confused because MS Access does not recognize my SQL code. I tried to do it in this way as well but it doesn't work:

SELECT FirstName, LastName, Email
FROM PATIENT
WHERE [LastName] LIKE 'K%' AND 'Ma%' OR [FirstName] LIKE 'K%' AND 'Ma%';

How can I make my code be recognized by Access and print the correct results?

CodePudding user response：

You can't skip mentioning the column name for each comparison you want to make. Otherwise something like [LastName] LIKE 'K%' OR 'Ma%' evaluates as [LastName] LIKE 'K%' OR true - because you're not comparing Ma% to anything else, so it's simply true on its own. And of course if you have true on either side of an OR then the whole clause ends up evaluating as true.

Also you've made a mess of the ANDs and ORs in the second example - e.g. the first name cannot start with K and start with Ma!

Lastly in MS Access the wildcard for LIKE is * not %.

This should be what you need.

WHERE 
  [LastName] LIKE 'K*' 
  OR [LastName] LIKE 'Ma*' 
  OR [FirstName] LIKE 'K*' 
  OR [FirstName] LIKE'Ma*';

Documentation: https://support.microsoft.com/en-us/office/like-operator-b2f7ef03-9085-4ffb-9829-eef18358e931

CodePudding user response：

It depends when you only want persons that start with K and Ma in the first and last name, you could use

SELECT FirstName, LastName, Email
FROM PATIENT
WHERE ([LastName] LIKE 'K*' OR [LastName] LIKE 'Ma*') 
  AND ([FirstName] LIKE 'K*' OR [FirstName] LIKE 'Ma*');

If you don't mind where the letter occurs, you better use this:

SELECT FirstName, LastName, Email
FROM PATIENT
WHERE [LastName] LIKE 'K*' OR [LastName] LIKE 'Ma*' 
   OR [FirstName] LIKE 'K*' OR [FirstName] LIKE 'Ma*';