From: migurus@yahoo.com   
      
   There is parent-child table situation and I am interested in getting only   
   cases when only one parent record exists, the matching criteria are in both   
   tables. There are one or two child records per each parent.   
      
   Running on SQL 2008, here is illustration, I omit key etc for brevity:   
      
   declare	@MAIN_NAME	varchar(32) = 'B%';   
   declare	@ATTR	int	= 124;   
      
   declare	@MAIN_RECORD	table	(   
   	MAIN_NAME	varchar(32)	not null	   
   ,	ID		int	not null   
   );   
      
   declare	@SUB_RECORD		table	(   
   	MAIN_ID		int	not null   
   ,	LOW_VAL		int	not null   
   ,	UP_VAL		int	not null   
      
   );   
      
   insert into @MAIN_RECORD	values   
    ('BETA-0'	,1)   
   ,('BETA-1'	,2)   
   ,('BETA-2'	,3);   
      
   insert into @SUB_RECORD		values   
   --	BETA-0   
    (	1,	100,	200	)   
   ,(	1,	101,	201	)   
   --	BETA-1   
   ,(	2,	1000,	1240	)   
   ,(	2,	1001,	1251	)   
   ,(	2,	1400,	1500	)   
   --	BETA-2   
   ,(	3,	1001,	1889	)   
   ,(	3,	1002,	1458	)   
      
      
      
   select M.MAIN_NAME   
   ,	S.LOW_VAL   
   ,	S.UP_VAL   
      
   from @MAIN_RECORD	M, @SUB_RECORD S   
   where	M.ID = S.MAIN_ID   
   and	M.MAIN_NAME like @MAIN_NAME   
   and	@ATTR	between S.LOW_VAL and S.UP_VAL;   
      
   result is   
   MAIN_NAME	LOW_VAL	UP_VAL   
   BETA-0	100	200   
   BETA-0	101	201   
   and this is good result, only one parent record found.   
      
   If I say @ATTR = 1424   
   result is   
   MAIN_NAME	LOW_VAL	UP_VAL   
   BETA-1	1400	1500   
   BETA-2	1001	1889   
   BETA-2	1002	1458   
   and I'd like NOT to output anything in this case, as there are two parent   
   records matching.   
      
   I can deal with this on the application side, where I would programmatically   
   count how many distinct MAIN_NAME came back and discard results when it is   
   more than one.   
      
   But I suspect there are ways to do it on the server side. Any thoughts would   
   be appreciated.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)