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

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

join with one parent record only

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — 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