List:General Discussion« Previous MessageNext Message »
From:Don Wieland Date:May 25 2012 7:58pm
Subject:Query weirdness...
View as plain text  
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
Thread
Query weirdness...Don Wieland25 May
  • Re: Query weirdness...Baron Schwartz26 May