I am hoping someone can bail me out on why this query in not working.
In the queries below:
query 1 returns multiple rows - one per client.
in the rows where:
client_id is 254240, dr_all_ther_qty = 1
client_id is 253821, dr_all_ther_qty = 1
client_id is 254023, dr_all_ther_qty = 6
But when I break out the queries into their own queries (queries
2,3,4), I get different result (WHICH are ACCURATE - the ones I need):
query 2 - dr_all_ther_qty = 0
query 3 - dr_all_ther_qty = 0
query 4 - dr_all_ther_qty = 5
Clueless on why these DO NOT reconcile. I really need query 1 results
to be the same results as queries 2,3, and 4. Little help!
--- start query 1 ---
SELECT q1.* FROM
(SELECT
apt.user_id,
apt.client_id,
c.last_name,
c.first_name,
MIN(apt.time_start) AS stime,
FROM_UNIXTIME(MIN(apt.time_start),'%Y-%m-%d') AS ftime,
(SELECT count(*) FROM tl_appt LEFT JOIN tl_rooms r on r.room_id =
tl_appt.room_id
WHERE client_id = apt.client_id AND r.location_id = '1' AND
appt_status_id = '3' AND time_start < MIN(apt.time_start)) AS previous,
(SELECT count(*) FROM tl_appt LEFT JOIN tl_rooms r on r.room_id =
tl_appt.room_id WHERE client_id = apt.client_id AND r.location_id =
'1' AND user_id = apt.user_id AND appt_status_id = '3' AND time_start
> '1293858000') AS dr_all_ther_qty
FROM tl_appt apt
LEFT JOIN tl_rooms r on r.room_id = apt.room_id
LEFT JOIN tl_clients c on c.client_id = apt.client_id
LEFT JOIN tl_users u on u.user_id = apt.user_id
WHERE
apt.appt_id IS NOT NULL AND
apt.time_start between '1293858000' and '1325393999' AND
apt.appt_status_id = '3' AND
apt.user_id = '506' and
r.location_id = '1'
GROUP BY apt.user_id, apt.client_id
ORDER BY u.last_name, u.first_name, c.last_name, c.first_name) as q1
WHERE q1.previous = 0
--- end query 1 ---
--- start query 2 ---
SELECT count(*) AS dr_all_ther_qty FROM tl_appt LEFT JOIN tl_rooms r
on r.room_id = tl_appt.room_id WHERE client_id = '254240' AND
r.location_id = '1' AND user_id = '506' AND appt_status_id = '3' AND
time_start > '1324927800'
--- end query 2 ---
--- start query 3 ---
SELECT count(*) AS dr_all_ther_qty FROM tl_appt LEFT JOIN tl_rooms r
on r.room_id = tl_appt.room_id WHERE client_id = '253821' AND
r.location_id = '1' AND user_id = '506' AND appt_status_id = '3' AND
time_start > '1318617000'
--- end query 3 ---
--- start query 4 ---
SELECT count(*) AS dr_all_ther_qty FROM tl_appt LEFT JOIN tl_rooms r
on r.room_id = tl_appt.room_id WHERE client_id = '254023' AND
r.location_id = '1' AND user_id = '506' AND appt_status_id = '3' AND
time_start > '1321903800'
--- end query 4 ---
Don Wieland
D W D a t a C o n c e p t s
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
