Select records with fallback check MySQL-CodePudding

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!

CodePudding user response：

If all you want to do is show the project when the project and the client are not hidding,you can just do c.hidden = 0 and p.hidden = 0.

CodePudding user response：

use join and where like below

     SELECT p.id, p.name as projectName, 
     c.name as clientName, p.rate 
    FROM Project p join Client c
    on p.client_id = c.id
   where  p.name NOT LIKE "%leave" 
   AND p.hidden = 0 and c.hidden=0
    ORDER BY c.name ASC, p.name ASC