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

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

Re: join with one parent record only

Show all headers | View raw

On Monday, December 3, 2012 10:50:02 PM UTC-8, bradbury9 wrote:
> 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

Thanks, but I can not use this approach, as application needs both child records if there are two of them

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