[SOLVED] JOIN pulling incorrect results

[benjam]

I have a simple JOIN query that is pulling (at least one, maybe more) results that are incorrect.

 

I have played around with the query and cannot seem to figure out why it is pulling these incorrect values.

 

Here is my query:

SELECT AUEG.appId
FROM AppUserGlue AS AUEG
	LEFT JOIN User AS UE
		ON UE.userId = AUEG.userId
WHERE UE.clientId = '{$clientId}'
	AND AUEG.createDate BETWEEN '{$periodStart}' AND '{$periodEnd}'
	AND AUEG.status = '".APP_USER_GLUE_ACTIVE."'
ORDER BY AUEG.appId

 

APP_USER_GLUE_ACTIVE = 1

APP_USER_GLUE_INACTIVE = 2

the $periods are unix timestamps and

the $clientId is INT

 

here is some sample data:

AUEG
appUserGlueId 	appId 	userId 	createDate 	status
374 		495 	20 	1170794281 	2	(inactive)
375 		495 	126 	1170795497 	1
376		823	12	1170791235	1


UE
userId	clientId
20	31
126	56
12	31

 

When I run the query with the clientId of 31 (and a period that is guaranteed to return all entries), it returns BOTH the appIds of 495 and 823 when it should only return one appId: 823, because the first one (495) doesn't have an active status flag to be pulled.

 

I modified the query to return everything form both tables and it seems to be pulling the correct user data, but it shouldn't.

i.e. - it pulls userId 20 and 12, although the appId related to userId 20 has an inactive status flag.

 

I also tried moving the conditional statement (AND UE.clientId = '{$clientId}') into the ON clause with disasterous results (it returned even more incorrect values).

 

I can't seem to figure out how to word this query to get it to function the way I intend it to.

 

Thanks for any help anybody can offer.

[benjam]

I'm an idiot.  The query above IS working correctly.

 

It was the subsequent queries that were pulling the bad information.

 

I was not testing the status flag correctly in the query that came after the above query.

 

Crisis averted, all is well.  Sorry to waste your time.

[fenway]

No problem at all...thanks for not deleting the post.