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

• peterm: Can you post the desired output based on the sample data provided?
• Gowri: @peterm: I added the expected output

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

• Gowri: This may work for m_type =2. But I need to write conditional where clause based on m_type
• DWils: I may be failing to grasp the full concept here, as I usually solve conditional logic in php, however, maybe you can use queries inside queries to achieve your goal? This would allow you to base the results of one query based on the query within the where clause. sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

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;

• Gowri: I am looking for solution in JOIN tables.

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