Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1359

Re: join with one parent record only

Show all headers | View raw

El martes, 4 de diciembre de 2012 00:42:39 UTC+1, migurus  escribió:
> 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.

You could use:

SELECT MAIN_NAME, MAX(LOW_VAL) AS LOW_VAL, MAX(UP_VAL) AS UP_VAL FROM SUB_RECORDS WHERE @ATTR BETWEEN LOW_VAL AND UP_VAL
GROUP BY MAIN_NAME HAVING COUNT(MAIN_NAME) = 1

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

join with one parent record only migurus <migurus@yahoo.com> - 2012-12-03 15:42 -0800
  Re: join with one parent record only bradbury9 <ray.bradbury9@gmail.com> - 2012-12-03 22:50 -0800
    Re: join with one parent record only migurus <migurus@yahoo.com> - 2012-12-04 23:07 -0800
      Re: join with one parent record only Erland Sommarskog <esquel@sommarskog.se> - 2012-12-05 22:19 +0100
        Re: join with one parent record only migurus <migurus@yahoo.com> - 2012-12-14 13:15 -0800

csiph-web