I have 4 tables in my database, a top layer followed by the other three, each one a layer deeper in terms of logic, linked via foreign keys.
4 booleans on the top layer control which rows should get selected in the second lowest level.
The result I am trying to achieve is getting the correct data based on these booleans, as well as a count of the lowest layer.
All tables and columns have been renamed for this example.
The booleans are (in this example) type_filter_1, type_filter_2, type_filter_3, type_filter_4. The naming of these booleans have nothing to do with the table names, they are internally linked with a string value in the
level_2 table. These strings are imported from an Excel file
Table level_4 (top layer) as no direct connections
Columns: - id - name - type_filter_1 - type_filter_2 - type_filter_3 - type_filter_4 ...
Table level_3 (second layer) has a direct connection to level_4
Columns: - id - level_4_id - name ...
Table level_2 (third layer) has a direct connection to level_4
Columns: - id - level_4_id ...
Table level_1 (bottom layer) has a direct connection to level_2 and level_3
Columns: - id - level_2_id - level_3_id ...
The query so far is:
SELECT COUNT(DISTINCT level_1.id) AS DP_ADDRESSES_COUNT, level_4.*, level_4.id AS id FROM `level_4` LEFT JOIN `level_3` ON `level_3`.`level_4_id` = `level_4`.`id` LEFT JOIN `level_1` ON `level_1`.`level_3` = `level_3`.`id` LEFT JOIN `level_2` ON `level_1`.`level_2_id` = `level_2`.`id` WHERE `level_4`.`id` = '4' AND CASE WHEN level_4.type_filter_1 = 0 THEN level_2.type != "KEY_1" ELSE TRUE END OR CASE WHEN level_4.type_filter_2 = 0 THEN level_2.type != "KEY_2" ELSE TRUE END OR CASE WHEN level_4.type_filter_3 = 0 THEN level_2.type != "KEY_3" ELSE TRUE END OR CASE WHEN level_4.type_filter_4 = 0 THEN level_2.type NOT IN("KEY_4", "KEY_5") ELSE TRUE END GROUP BY `level_4`.`id` LIMIT 1;
Any help with this query will be greatly appreciated!
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.
PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites.
The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
DBMS is a database management system. It is designed to change, search, add and delete information in the database. There are many DBMSs designed for similar purposes with different features. One of the most popular is MySQL.
It is a software tool designed to work with relational SQL databases. It is easy to learn even for site owners who are not professional programmers or administrators. MySQL DBMS also allows you to export and import data, which is convenient when moving large amounts of information.
Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.
Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.
Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.