Mysql query with if exists check in where clause

Question

I have two tables (A and B) where my query compares a calculation from table A with a range in table B and then insert a corresponding value to the range(also in table B) in the third table(table C) based on dates. However,it is a possibility that table A may not have data for everyday and for those days i want to enter the value against the second lowest range.

TABLE B
id(PK)|date| v1 | v2


TABLE A
Aid|id(FK)|MinRange|MaxRange|Value


TABLE C
Cid|b.date|id(FK)|b.v1|b.v2|A.value

I am looking for a way to embed IF EXISTS in the WHERE clause something like this:

SELECT B.value 
from TableB B 
INNER JOIN TableA A ON A.id=B.id 
WHERE B.id=4 
and (IF DATA EXISTS) B.v1+B.v2 between A.min and A.max (ELSE Choose the second lowest A.min)`

The query above is an example to explain what i am trying to do, hence, it is not a valid query. I do not want to use a subquery for obvious performance issues.

I will appreciate any help.Thanks in advance :)

Can you post the structure of the tables? It will help people to answer better to your question. — Chamal
– Chamal, Commented Sep 18, 2013 at 6:47
how?adding where b.date is not null will still skip insertion of non existent records in b. i want to include the non existent with a predefined value in table a. — Salik
– Salik, Commented Sep 18, 2013 at 7:06

Chamal · Accepted Answer · 2013-09-18 09:50:10Z

2

What about this

UPDATE

DECLARE myvar INT;
SELECT COUNT(*) INTO myvar FROM TableB WHERE Date=p_date;
SELECT myvar;

IF(myvar>0)
    SELECT B.Date,A.Value
    from TableB B 
    left JOIN TableA A ON A.id=B.id 
    WHERE B.id=4 
ELSE
    SELECT p_date AS Date,predefinedvalue AS Value
END IF;

You have to use left join because inner join will retrieve only the matching records. Also the predefined value has to be stored in B since if there is no matching record you can't query anything from A.

SELECT CASE WHEN myvar>0 THEN B.Date
       ELSE p_date
       END AS Date,
       CASE WHEN myvar>0 THEN A.Value
       ELSE predefinedvalue
       END AS Value
from TableB B 
left JOIN TableA A ON A.id=B.id 
WHERE B.id=4

edited Sep 18, 2013 at 9:50

answered Sep 18, 2013 at 7:23

Chamal

1,44910 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Salik Over a year ago

it wont, because it wont check the case for the dates that does not exist in table b.hence, skipping it. i have to check that if a date does not exist, the predefined value be defined with the given date parameter.

Chamal Over a year ago

How are you checking whether date is not exist in table B? Is there another table?

Salik Over a year ago

date is provided as a parameter(lets say p_date), its a part of a stored procedure.

Salik Over a year ago

this might work, i'll mark it as a right answer if i can not do it all in a single query. do you think it can be done in a single query?

Chamal Over a year ago

IF ELSE can be removed with a case statement as i shown in the updated answer if that what you want.

Collectives™ on Stack Overflow

Mysql query with if exists check in where clause

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related