[RESOLVED] SELECT DISTINCT problem

[cretaceous]

I've got what should be a simple nested SELECT DISTINCT query, querying a single table but it's not working

I can't see what is wrong .. anyone spot the problem ?

SELECT DISTINCT u_id FROM user_form_entries WHERE (cfent_id='502' AND ufe_txt='Male' ) AND (cfent_id='522' AND ufe_txt='Yes' )

if I go into phpMyAdmin there are matches on those two items, where u_id=7 but they dont give a result
(I've checked for random whitespace)

the query is put together programmatically and it works ok with a single value as here:

SELECT DISTINCT u_id FROM user_form_entries WHERE (cfent_id='502' AND ufe_txt='Male' )

[dagon]

is cfent_id unique per table? did you mean OR not AND?

[cretaceous]

no, cfent_id is not unique
unfortunately I do mean AND - it's a very selective search

and I've now realised there are no rows that can be returned using AND - but AND is what I need..

I think I need to do joins onto itself, as an alias, for each new subclause - does that sound right?

currently I've got a hacky thing with arrays working (after doing the search with OR to link the subclauses) but it's not nice

[johanafm]

The where clause you have uses only AND, which means that the parentheses aren't needed. I.e. your where clause is identical to this one

cfent_id='502' AND ufe_txt='Male' AND cfent_id='522' AND ufe_txt='Yes'

If I were to guess, you want to retrieve a user id that has both of these
One record of cfent_id=502 and ufe_txt='Male'
One record of cfent_ud=522 and ufe_text='Yes'

And as far as I can tell, that means you have two alternatives. One is joining on a subquery

SELECT uid
FROM table 
	INNER JOIN (
		SELECT uid
		FROM table
		WHERE cfent_id=522 AND ufe_txt='Yes') as t
	ON table.uid = t.uid	
WHERE cfent_id=502 AND ufe_txt='Male'

And the other is using group by and having

SELECT u_id, COUNT(*) AS uid_count
FROM user_form_entries
WHERE (cfent_id='502' AND ufe_txt='Male' ) OR
		(cfent_id='522' AND ufe_txt='Yes' ) 
GROUP BY u_id
HAVING uid_count > 1

[cretaceous]

thanks - inner joins (or my array hack) are the solution