Nested Query With HAVING statement HELP!

[aco636]

Hi there
AS always any help gratefully received.

I have a problem filtering records in a query with nested query. As you will see from the SQL below, I want to filter records based on a nested query with a record count < 2.
For some reason I cant work out, the result is ignoring the HAVING COUNT(*) < 2. Can anyone see an obvious problem?

Regards ACO

SELECT --COUNT(*)
	e.SerialNo, Field1, Field2
FROM ETable e INNER JOIN MTable m ON e.SerialNo = m.SerialNo
WHERE e.Field1 = 2
AND m.Field2 = 1
AND e.SerialNo IN 
	(
	SELECT SerialNo
	FROM UTable
	WHERE Field1 <> 1
	GROUP BY SerialNo HAVING COUNT(*) < 2

 

[JamesLean]

Apart from the missing closing parenthesis I can't see anything obviously wrong with the query. Can you post some sample data and what results you want?

--James

 

[aco636]

Thanks for the reply, not sure I can post the data but as I say in essence it appears to be ignoring the HAVING clause.
THe query returns all records in the nested query but ignoring the HAVING COunt(*) < 2

Regards ACO

 

[Katy44]

when you run the subquery:

SELECT SerialNo
FROM UTable
WHERE Field1 <> 1
GROUP BY SerialNo HAVING COUNT(*) < 2

Does it still ignore this condition?

can you output the count(*):
SELECT SerialNo, count(*)
FROM UTable
WHERE Field1 <> 1
GROUP BY SerialNo HAVING COUNT(*) < 2


and

SELECT SerialNo, count(*)
FROM UTable
WHERE Field1 <> 1
GROUP BY SerialNo order by count(*) asc

 

[aco636]

Thanks for the replies.

I fianlly worked out what was going on. For my query to return the expected results, the filter in the nested query was the problem. I added uTable as a LEFT JOIN to the main query and added the filter there. I kept the nested query without the filter. THis cured the problem.
Many thanks for all your help.
Regards ACO