CODEWITHSUNDEEP
mysqlsqldatabaseselect
Donovan Keating
Donovan Keating

Reputation: 1885

MySQL: A query that finds active customers in the database

I need help figuring out which query to run to search for a customer. I'm building a "money loaning" program which has a search functionality. The user can filter the customers displayed in the system through customerFirstName, customerSecondName, and whether or not they're active.

The thing is, there is no status column in the customer table. A costumer is considered Active if one or more of their accounts have status = 'Active'. A customer is considered Inactive if they have not opened any accounts OR all their accounts have status = 'Paid'

My SELECT statement is:

SELECT 
customer.customerID,
customerFName,
customerLName,
gender,
civilStatus,
birthDate,
homeAddress,
jobDescription,
workingAddress,
telNumber,
phoneNumber,
pinNumber

A customer is filtered by first and last names through this condition:

WHERE customerFName LIKE '%*userProvidedFirstName*%' AND customerLName LIKE '%*userProvidedLastName*%'

This is my database schema:

This is my database ERD

Upvotes: 0

Views: 595

Answers (1)

Garrett
Garrett

Reputation: 21

You could try getting the status in the select portion and then filtering based on a sub select: 

select * from
    (SELECT 
    customer.customerID,
    customerFName,
    customerLName,
    gender,
    civilStatus,
    birthDate,
    homeAddress,
    jobDescription,
    workingAddress,
    telNumber,
    phoneNumber,
    pinNumber
    case when (select count(*) from account where account.customerID = customerID and account.status = 'Active') > 0 
    then 'Active' else 'Inactive' end as status
from customer)
    WHERE customerFName LIKE '%*userProvidedFirstName*%' AND customerLName LIKE '%*userProvidedLastName*%' AND status = *Status*

Upvotes: 1

Related Questions