how to write conditional where clause query

Question

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

Answer 1

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.

Answer 2

Try something like this:

WHERE DATE('m_date') > NOW() - INTERVAL 1 DAY.

It would be best if the time stored is in a timestamp type column as those are easier to compare to the NOW() function.

Documentation:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Answer 3

i think following one may be help to you..

declare
x number;
begin
x := &x;
dbms_ouptut.put_line('Enter post id',&x);
for i in (select max(value) from tb2 where key not in('m_date') and post_id = x group by id,post_id,key)
loop
curdate() = (select value from tb2 where key = 'm_date' and post_id = x) + i;
end loop;
end;

Answer 4

Please refer below links, you will get idea.

http://use-the-index-luke.com/sql/where-clause/obfuscation/smart-logic

https://stackoverflow.com/questions/12264342/conditional-where-clause-any-way-to-write-except-dynamic-query

https://stackoverflow.com/questions/11338775/conditionally-use-where-clause-in-sql

http://www.codeproject.com/Questions/489046/Howplustoplusapplyplusconditionalplusstatementplus