CODEWITHSUNDEEP
sqlmariadblogic
Jonas
Jonas

Reputation: 1205

A way to condition JOIN lookup tables based on the id value of a (parent) lookup table [MariaDB]

I am trying to wrap my head around how to write a statement that can fetch data from multiple lookup tables but also incorporate an if condition that triggers certain JOINS based on the value of the (parent) lookup table.

Explaination:

I have one main table I insert records into the DB via lookup table lists already added to DB. This table uses multiple lookup tables and inner joins to display the lookup tables values.

Recently I broke up the data in months to separate weeks lookup tables (weeks within months of the year) but now my problem is selecting from these new 12 separate tables that use to all be in 1.

Below is the simplified statement I used before the weeks table became 12 different tables

    SELECT main_table.id AS `id`,
    month_table.month AS `month`,
    week_table.week AS `week`,
    FROM main_table
    INNER JOIN month_table AS month_table ON main_table.month = month_table.id
    INNER JOIN week_table AS week_table ON main_table.day = week_table.id

Now I'm looking for something like:

    SELECT main_table.id AS `id`,
    -- month_table.month AS `month`,

    if(month_table.month==1)
    {
       INNER JOIN Jan_weeks AS Jan_weeks ON main_table.jan_weeks = Jan_weeks.id
    }

    if(month_table.month==2)
    {
       INNER JOIN Feb_weeks AS Feb_weeks ON main_table.feb_weeks = Feb_weeks.id
    }
    ...
    week_table.week AS `week`,
    FROM main_table
    -- INNER JOIN month_table AS month_table ON main_table.month = month_table.id
    INNER JOIN week_table AS week_table ON main_table.day = week_table.id
    ...

TABLES

Main Lookup Table

Table That must be referenced to before Sub Lookup Table 1 & Sub Lookup Table 2

id (AI PK) Months timestamp
1 January 2025-02-28 14:45:11
2 February 2025-02-28 14:45:11
... ... ...

Sub Lookup Table 1

id (AI PK) jan_weeks timestamp
1 Week 1 something happens 2025-02-28 14:45:11
2 Week 2 something different 2025-02-28 14:45:11
... ... ...

Sub Lookup Table 2

id (AI PK) feb_weeks timestamp
1 Week 1 something different 2025-02-28 14:45:11
2 Week 2 something different 2025-02-28 14:45:11
... ... ...

...

Main Table

id (AI PK) Month Week ... timestamp
1 1 1 ... 2025-02-28 14:45:11
2 2 2 ... 2025-02-28 14:45:11
... ... ...

Example of table that I want returned using lookup tables

id (AI PK) Month Week ... timestamp
1 January Week 1 something happens ... 2025-02-28 14:45:11
2 February Week 2 something different ... 2025-02-28 14:45:11
... ... ...

I'm not sure that my current thinking is the correct method or if there's a better way to do this.

Any help or suggestions would be greatly appreciated.

Database Details

[Content]
Network type: MariaDB or MySQL (TCP/IP)
Connected: Yes
Server OS: debian-linux-gnu
Server version: 10.5.27-MariaDB-deb11 - mariadb.org binary distribution
Compressed protocol: No
Unicode enabled: Yes
Threads: 107
Questions: 237,853,342
Slow queries: 4
Opens: 2,761,049
Open tables: 2,000
Queries per second avg: 1,027.484

Upvotes: 1

Views: 34

Answers (1)

Guillaume Outters
Guillaume Outters

Reputation: 1607

With your current structure

You may create a view to emulate the old single week_table.

Or, if your need is only for one query, use a Common Table Expression (CTE) that acts as a view / virtual table for one query only.

For that view or CTE, a UNION ALL will put all rows of the 12 tables together,
here with a CTE (the WITH keyword):

WITH week_table AS
(
    SELECT 1 AS `month`, w.* FROM Jan_weeks w
    UNION ALL
    SELECT 2, w.* FROM Feb_weeks w
    UNION ALL
    […]
)
SELECT main_table.id AS `id`,
month_table.month AS `month`,
week_table.week AS `week`
FROM main_table
INNER JOIN month_table AS month_table ON main_table.month = month_table.id
INNER JOIN week_table AS week_table ON main_table.week = week_table.id AND main_table.month = week_table.month;

Here a working example with your data for January and February.

By going back to the monolithic table

As said in the comments, if your 12 tables have the same structure, it may be better to let them in 1 table.

You may want to consider partitioning that table to distribute data by month (for performance reasons for example).

Upvotes: 0

Related Questions