MySQL Or condition on two columns that may be empty-CodePudding

I have a table customers that has a column email_address and contact_number and only one of them is required.

Now I want to write a Query to check if the customer exists.

This is my query so far:

SELECT * 
FROM customer c
WHERE (c.email_address = '' OR c.contact_number = '0123456789')
AND c.company_id = companyId

This works fine if the customer record have both a email address and contact number, but if the customer only has one of them (e.g. email_address = '' and contact_number = '1234567890' ) it returns a result which is correct, but I would like it to not check the column if email_address or contact_number is empty or null.

Edit: Here is some sample data and examples of the result I would like:

Table: customer

id, email_address, contact_number, company_id

1, '', '0123456789', 1

2, '[email protected]', '', 1

3, '[email protected]', '2345678901', 1

if I give the query the email address: '[email protected]' and contact number: '' it must return customer 2

if I give the query the email address: '' and contact number: '2345678901' it must return customer 3

if I give the query the email address: '[email protected]' and contact number: '0123456789' it must return customer 1 and 3

if I give the query the email address: '[email protected]' and contact number: '2345678901123123132' it must return customer 3

if I give the query the email address: '[email protected]' and contact number: '234567890112312313223' it must return no customer

CodePudding user response：

What you're probably searching for:

Input: EMAIL and PHONE

Query:

SELECT * 
FROM customer c
WHERE
(
     (c.email_address = 'EMAIL' AND 'EMAIL' <> '')
  OR (c.contact_number = 'PHONE' AND 'PHONE' <> '')
)
AND c.company_id = companyId