I have 3 table join where I want to select all records from parent-table "ListOrder" but restrict child-table "Count" to only those that are recent (about 6 months) but having a little problem with it. FYI, the third table "List" is used for lookup and for second criteria of selecting matches only specific to certain ManagerID.

The query is running again MS SQL Server. Here's the query:

SELECT ListOrder.ListID, List.Listname, Count.Updated
	FROM ListOrder 
	LEFT OUTER JOIN Count ON
	ListOrder.ListID=Count.ListID AND ListOrder.SelectID=Count.SelectID
	JOIN List ON
	ListOrder.ListID=List.ListID
	WHERE List.ManagerID = 1875
	GROUP BY ListOrder.ListID, List.Listname, Count.Updated
	HAVING Count.Updated >= GetDate()-180

Now, if I leave out the HAVING clause, it works but I need to restrict the Updated date to just last 6 months or so. I tried first using WHERE clause instead of HAVING but didn't work.

Anyone have an answer? Thanks!