Select records with fallback check MySQL

February 14, 2022

I have a SELECT query which gives me some projects and clients from my database.

My initial query is at follows:

SELECT p.id, p.name as projectName, c.name as clientName, p.rate 
        FROM Project p, Client c
        WHERE p.client_id = c.id AND p.name NOT LIKE "%leave" AND p.hidden != 1
        ORDER BY c.name ASC, p.name ASC

As you can see if a project has the hidden column as `1 (true) then I don’t want to show it.

Now I have added this hidden column also on Clients table. This is because if a client is hidden then I don’t want to show the projects that are assigned to it.

How should I transform my query if a project has hidden = 0 to check if the client has hidden = 1 and don’t show it.

I tried something like this:

SELECT p.id, p.name as projectName, c.name as clientName, p.rate 
        FROM Project p, Client c
        WHERE p.client_id = c.id AND p.name NOT LIKE "%leave" AND (c.hidden != 1 OR p.hidden != 1)
        ORDER BY c.name ASC, p.name ASC

I guess is something like a fallback, if project.hidden = 0 also check for client.hidden and if client.hidden = 1 then don’t show the record, but if the client.hidden = 0 then show the record.

Here are my tables:

Client

Project

Update with dummy data

Let’s say these are my clients:

And these are the projects:

In the end I need the get the projects that neither them or the client that are assigned to them are not hidden: Project X, Project Z, Project W and Project B.

Because the other project are either hidden or the client which is assigned to them is hidden.

Solution

I went so far for a simple answer. Here is the solution:

SELECT p.id, p.name as projectName, c.name as clientName, p.rate 
            FROM Project p, Client c
            WHERE p.client_id = c.id AND p.name NOT LIKE "%leave" AND c.hidden != 1 AND p.hidden != 1
            ORDER BY c.name ASC, p.name ASC

Thank you for your time!