1

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

  • Can you post the desired output based on the sample data provided? – peterm Nov 23 '13 at 22:35
  • @peterm: I added the expected output – Gowri Nov 24 '13 at 6:53
4
+50

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.

0

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

  • This may work for m_type =2. But I need to write conditional where clause based on m_type – Gowri Nov 25 '13 at 4:34
  • 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 – DWils Nov 25 '13 at 8:01
0

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;
  • I am looking for solution in JOIN tables. – Gowri Nov 25 '13 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.