Selection Query

JayKappy — Fri, 10 Dec 2021 22:58:13 GMT

I am using this SQL to run a query to find lines that are the same length (THIS WORKS BY ITSELF)

[Length] In (SELECT [Length] FROM [wMain] GROUP BY [Length] HAVING Count(*)>1 )

What I want to add to it is this (THIS WORKS BY ITSELF)

[Material] IS NOT NULL AND [Util_ID] IS NOT NULL

I tried this but getting an error...ANYONE out there know the correct syntax?
DO I use a WHERE, if so how?

[Length] In (SELECT [Length] FROM [wMain] GROUP BY [Length] HAVING Count(*)>1 AND [Material] IS NOT NULL AND [Util_ID] IS NOT NULL)

Thanks.

Re: Selection Query

SamHaycraft — Mon, 13 Aug 2012 04:36:37 GMT

In SQL your GROUP BY and HAVING clauses apply only to the aggregates returned and must be the last statements. You need to first filter out your nulls with a where clause, like so:

[Length] In (SELECT [Length] FROM [wMain] WHERE [Material] IS NOT NULL AND [Util_ID] IS NOT NULL GROUP BY [Length] HAVING Count(*)>1 AND )

topic Re: Selection Query in Data Management Questions

Selection Query

Re: Selection Query