Mysql – how to write conditional where clause query

MySQL

Hi I want write the mysql query conditionally. I am basically using the WordPress, please have a look at my same tables.

tbl1

id      name

1   A
2   B
3   C
4   D
5   E

tbl2

id    post_id   key             value

1   1   m_type          1
2   1   m_date          2013-11-22
3   2   m_type          2
4   2   m_date          2013-11-20
5   2   m_routine       2
6   3   m_type          2
7   3   m_date          2013-11-10
8   3   m_routine       null
9   3   m_custom_rountine   5

I have the maintenance date in tbl2. So I want to get the all the post id filtered by following types.

Maintenance

  1. Due today
  2. Week due.
  3. Year due.
  4. over due.

tbl2 has the due date m_date. But My problem when m_type = 1. I need to add the where clause like WHERE CURDATE() = m_date else m_type =2 means m_date will be the starting date of maintenance and I need to increment the date using m_routine or m_custom_routine.

For example,
m_type = 2
m_date = 2013-11-22
m_routine = 2

Then I want check

CURDATE() = 2013-11-22 OR 2013-11-24 OR 2013-11-26 etc.

I was thinking to use CASE .. WHEN... THEN in WHERE clause. But I am not getting it properly.

Desired output

CURDATE() = 2013-11-22

Output post_id = 1,2 (2-routine incremented date from 2013-11-20)

CURDATE() = 2013-11-15

Output post_id = 3 (custom_routine incremented date from 2013-11-10)
Hope I explained well enough. Please someone help me.

SUDO code

if(m_type == 1){
    m_date = CURDATE();
}
else if(m_type == 2){
    increment_date = m_date increment m_rountine (OR) m_date increment m_custom_rountine
    CURDATE() = increment_date  
}

Edit:-

I created the sql fiddle for your ref

Best Answer

It can be done using some multiple joins from table1 into table2 and then use those joins combined with case/calculation logic, or using sub-selects. But it seems rather complex to want to do that in a single query/single join - that I'd recommend finding an alternative way to do it. It's something which is a good candidate to solve in application logic.

However, I think the route you want to go is something like this:

SELECT
  T1.*,
  CASE
    WHEN T2.value = '1' THEN 'CalculateStuffBasedOnT3OrT4'
    WHEN T2.value = '2' THEN 
        CASE         
          WHEN t3.value IS NULL THEN 'CalculateOtherStuffBasedOnT3OrT4'
          WHEN T3.value = '2' THEN 'CalculateMoreStuffBasedOnT3OrT4'
          ELSE 'SomethingElse'
        END
     ELSE 'Something'
    END
FROM tbl1 T1
LEFT JOIN tbl2 T2 ON T1.id = T2.id AND T2.key = 'm_type'
LEFT JOIN tbl2 T3 ON T1.id = T3.id AND T3.key = 'm_routine'
LEFT JOIN tbl2 T4 ON T1.id = T4.id AND T4.key = 'm_date'

Such a query can then be used as foundation in a subquery to select the posts you want.

But this will quickly become quite complex and unmaintainable. So I'd advice you to rethink the strategy and go with designing the query dynamically in you php code, or rethink the data structure, or utilize multiple queries to solve it.

Related Question