if statement - MySQL logic where function I think -

i have 500 rows simliar data 4 rows below, exmaple want 2 rows returned using following logic:

if emp no 817 sal_id = 2 is_active 0 return 817 sal_id 1 or if emp no 820 sal_id = 2 is_active 1 return 820 sal_2

so sal_id 1 active default , sal_id 2 should returned instead of sal_id 1 if is_active 1, hope makes sense!

 +--------+--------+-----------+-----------------------+-----------+ | emp no | sal_id |   value   |    effective_date     | is_active | +--------+--------+-----------+-----------------------+-----------+ |    817 |      1 | ded914e3b | 01/04/2013            |         1 | |    817 |      2 | 0         | 0                     |         0 | |    820 |      1 | 8238942be | 02/04/2013            |         1 | |    820 |      2 | ea42574e4 | 02/04/2013            |         1 | +--------+--------+-----------+-----------------------+-----------+ 

giving me result of:

 +--------+--------+-----------+----------------+-----------+ | emp no | sal_id |   value   | effective_date | is_active | +--------+--------+-----------+----------------+-----------+ |    817 |      1 | ded914e3b | 01/04/2013     |         1 | |    820 |      2 | ea42574e4 | 02/04/2013     |         1 | +--------+--------+-----------+----------------+-----------+ 

not sure can done, please advise , thanks.

this solution more verbose mark's, should more efficient, since avoids using temp table...

select     es1.emp_no,     if(es2.is_active=1, es2.sal_id, es1.sal_id) sal_id,     if(es2.is_active=1, es2.value, es1.value) value,     if(es2.is_active=1, es2.effective_date, es1.effective_date) effective_date,     if(es2.is_active=1, es2.is_active, es1.is_active) is_active employee_salary es1     left join employee_salary es2 on (es2.emp_no=es1.emp_no , es2.sal_id=2) es1.sal_id=1; 

...which yields...

+--------+--------+-----------+----------------+-----------+ | emp_no | sal_id | value     | effective_date | is_active | +--------+--------+-----------+----------------+-----------+ |    817 |      1 | ded914e3b | 01/04/2013     |         1 | |    820 |      2 | ea42574e4 | 02/04/2013     |         1 | +--------+--------+-----------+----------------+-----------+ 

...and explain yields...

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows |       | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ |  1 | simple      | es1   |  | null          | null | null    | null |    4 | using | |  1 | simple      | es2   |  | null          | null | null    | null |    4 |             | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 

...compared mark's...

+----+-------------+-----------------+------+---------------+------+---------+------+------+----------------------------------------------+ | id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows |                                        | +----+-------------+-----------------+------+---------------+------+---------+------+------+----------------------------------------------+ |  1 | primary     | <derived2>      |  | null          | null | null    | null |    2 |                                              | |  1 | primary     | es              |  | null          | null | null    | null |    4 | using where; using join buffer               | |  2 | derived     | employee_salary |  | null          | null | null    | null |    4 | using where; using temporary; using filesort | +----+-------------+-----------------+------+---------------+------+---------+------+------+----------------------------------------------+ 

you can strip out of if clauses don't need values.