Need query result that doesn't start and end with "A" or "a" letter-CodePudding

I am trying to write a query that returns list of customer names that do not start or end with letter "A" (either in upper case or lower case). Following is my query,

SELECT * FROM Customers 
WHERE 
CustomerName IN (SELECT * FROM Customers WHERE CustomerName NOT LIKE 'A%') 
AND 
CustomerName IN (SELECT * FROM Customers WHERE CustomerName NOT LIKE '%a')

In the query result, I am getting an "Error Code: 1241. Operand should contain 1 column(s)". Columns of Customers table is

`customers` (
  `CustomerID` int(11) NOT NULL,
  `CustomerName` varchar(255) DEFAULT NULL,
  `ContactName` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL,
  `PostalCode` varchar(255) DEFAULT NULL,
  `Country` varchar(255) DEFAULT NULL

Can someone please correct my query?

CodePudding user response：

Using a sub query adds is like adding extra calculation which will utilize more resources.

This can be achieved using a simple where clause like this:

select
    *
from
    Customers
where
    upper(CustomerName) not like 'A%'
    and upper(CustomerName) not like '%A'

CodePudding user response：

SELECT CustomerName FROM Customers 
WHERE 
CustomerName NOT LIKE 'A%'
AND 
CustomerName NOT LIKE '%a'

you dont need subquery.

if you want to use subquery you code should like bellow,

SELECT * FROM Customers 
WHERE 
CustomerName IN (SELECT CustomerName FROM Customers WHERE CustomerName NOT LIKE 'A%') 
AND 
CustomerName IN (SELECT  CustomerName FROM Customers WHERE CustomerName NOT LIKE '%a')

"But in here you dont need that.

"So you should define in subquery what you want after the SELECT.

CodePudding user response：

Please try this way, It will take care of case sensitive as well.

SELECT * FROM customers WHERE CustomerName  NOT LIKE 'A%'  AND CustomerName  NOT LIKE '%A'

You don't need to perform subquery for this.