Description:
A query which contains if logic and a left outer join is not producing the desired results.
How to repeat:
Run the following query:
SELECT dept_id,
if (month(a.INCIDENT_DATE) = '11',sum(points),0) as pointsnov,
if (month(a.INCIDENT_DATE) = '12',SUM(POINTS),0) AS pointsdec
FROM attendance_respond b left outer join nfirs_1abcde a on
b.incident_no = a.incident_no
and b.fdid = a.fdid
and b.station = a.station
group by b.dept_id
order by b.dept_id
Dept_id 61 and 62 should have a number other than zero in the pointsnov column. I have attached the database and table source for your convenience.
Results:
+---------+-----------+-----------+
| dept_id | pointsnov | pointsdec |
+---------+-----------+-----------+
| 29 | 0 | 2 |
| 48 | 0 | 2 |
| 54 | 0 | 2 |
| 59 | 0 | 2 |
| 60 | 8 | 0 |
| 61 | 0 | 0 |
| 62 | 0 | 0 |
| 81 | 0 | 2 |
| 96 | 0 | 2 |
| 97 | 0 | 2 |
+---------+-----------+-----------+
when I run the following query, the display shows data which should meet the criteria of the query above.
SELECT dept_id, incident_date,points
FROM attendance_respond a, nfirs_1abcde b
where b.incident_no = a.incident_no
order by dept_id, incident_date
+---------+---------------+--------+
| dept_id | incident_date | points |
+---------+---------------+--------+
| 29 | 2003-12-11 | 2 |
| 48 | 2003-12-11 | 2 |
| 54 | 2003-12-11 | 2 |
| 59 | 2003-12-11 | 2 |
| 60 | 2003-11-05 | 2 |
| 60 | 2003-11-05 | 2 |
| 60 | 2003-11-05 | 2 |
| 60 | 2003-11-06 | 2 |
| 61 | 2003-09-01 | 2 |
| 61 | 2003-11-05 | 2 |
| 61 | 2003-11-05 | 2 |
| 61 | 2003-11-05 | 2 |
| 61 | 2003-11-06 | 2 |
| 61 | 2003-11-06 | 2 |
| 62 | 2003-09-01 | 2 |
| 62 | 2003-11-05 | 2 |
| 62 | 2003-11-05 | 2 |
| 62 | 2003-11-06 | 2 |
| 81 | 2003-12-11 | 2 |
| 96 | 2003-12-11 | 2 |
| 97 | 2003-12-11 | 2 |
+---------+---------------+--------+
Suggested fix:
not sure.
Description: A query which contains if logic and a left outer join is not producing the desired results. How to repeat: Run the following query: SELECT dept_id, if (month(a.INCIDENT_DATE) = '11',sum(points),0) as pointsnov, if (month(a.INCIDENT_DATE) = '12',SUM(POINTS),0) AS pointsdec FROM attendance_respond b left outer join nfirs_1abcde a on b.incident_no = a.incident_no and b.fdid = a.fdid and b.station = a.station group by b.dept_id order by b.dept_id Dept_id 61 and 62 should have a number other than zero in the pointsnov column. I have attached the database and table source for your convenience. Results: +---------+-----------+-----------+ | dept_id | pointsnov | pointsdec | +---------+-----------+-----------+ | 29 | 0 | 2 | | 48 | 0 | 2 | | 54 | 0 | 2 | | 59 | 0 | 2 | | 60 | 8 | 0 | | 61 | 0 | 0 | | 62 | 0 | 0 | | 81 | 0 | 2 | | 96 | 0 | 2 | | 97 | 0 | 2 | +---------+-----------+-----------+ when I run the following query, the display shows data which should meet the criteria of the query above. SELECT dept_id, incident_date,points FROM attendance_respond a, nfirs_1abcde b where b.incident_no = a.incident_no order by dept_id, incident_date +---------+---------------+--------+ | dept_id | incident_date | points | +---------+---------------+--------+ | 29 | 2003-12-11 | 2 | | 48 | 2003-12-11 | 2 | | 54 | 2003-12-11 | 2 | | 59 | 2003-12-11 | 2 | | 60 | 2003-11-05 | 2 | | 60 | 2003-11-05 | 2 | | 60 | 2003-11-05 | 2 | | 60 | 2003-11-06 | 2 | | 61 | 2003-09-01 | 2 | | 61 | 2003-11-05 | 2 | | 61 | 2003-11-05 | 2 | | 61 | 2003-11-05 | 2 | | 61 | 2003-11-06 | 2 | | 61 | 2003-11-06 | 2 | | 62 | 2003-09-01 | 2 | | 62 | 2003-11-05 | 2 | | 62 | 2003-11-05 | 2 | | 62 | 2003-11-06 | 2 | | 81 | 2003-12-11 | 2 | | 96 | 2003-12-11 | 2 | | 97 | 2003-12-11 | 2 | +---------+---------------+--------+ Suggested fix: not sure.