Received: by 10.66.75.230 with SMTP id f6mr3285978paw.25.1354691267123; Tue, 04 Dec 2012 23:07:47 -0800 (PST)
Received: by 10.50.108.200 with SMTP id hm8mr324101igb.10.1354691267065; Tue, 04 Dec 2012 23:07:47 -0800 (PST)
Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!kr7no7430602pbb.0!news-out.google.com!6ni25806pbd.1!nntp.google.com!kr7no7430595pbb.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-sqlserver
Date: Tue, 4 Dec 2012 23:07:46 -0800 (PST)
In-Reply-To: <7fe453d0-4aec-41e0-91bd-77a5d3e08792@googlegroups.com>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=97.94.183.38; posting-account=PG2dbQkAAADVILsQ0GhgAM6hZK18SIjs
NNTP-Posting-Host: 97.94.183.38
References: <793b9352-0831-43e1-b48d-bf993714ba21@googlegroups.com> <7fe453d0-4aec-41e0-91bd-77a5d3e08792@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <00bf8bb5-dafc-426f-b228-e62ba1fbfa28@googlegroups.com>
Subject: Re: join with one parent record only
From: migurus <migurus@yahoo.com>
Injection-Date: Wed, 05 Dec 2012 07:07:47 +0000
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Xref: csiph.com comp.databases.ms-sqlserver:1360

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=F3:
>=20
> > There is parent-child table situation and I am interested in getting on=
ly cases when only one parent record exists, the matching criteria are in b=
oth tables. There are one or two child records per each parent.
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > Running on SQL 2008, here is illustration, I omit key etc for brevity:
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > declare	@MAIN_NAME	varchar(32) =3D 'B%';
>=20
> >=20
>=20
> > declare	@ATTR	int	=3D 124;
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > declare	@MAIN_RECORD	table	(
>=20
> >=20
>=20
> > 	MAIN_NAME	varchar(32)	not null=09
>=20
> >=20
>=20
> > ,	ID		int	not null
>=20
> >=20
>=20
> > );
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > declare	@SUB_RECORD		table	(
>=20
> >=20
>=20
> > 	MAIN_ID		int	not null
>=20
> >=20
>=20
> > ,	LOW_VAL		int	not null
>=20
> >=20
>=20
> > ,	UP_VAL		int	not null
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > );
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > insert into @MAIN_RECORD	values
>=20
> >=20
>=20
> >  ('BETA-0'	,1)
>=20
> >=20
>=20
> > ,('BETA-1'	,2)
>=20
> >=20
>=20
> > ,('BETA-2'	,3);
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > insert into @SUB_RECORD		values
>=20
> >=20
>=20
> > --	BETA-0
>=20
> >=20
>=20
> >  (	1,	100,	200	)
>=20
> >=20
>=20
> > ,(	1,	101,	201	)
>=20
> >=20
>=20
> > --	BETA-1
>=20
> >=20
>=20
> > ,(	2,	1000,	1240	)
>=20
> >=20
>=20
> > ,(	2,	1001,	1251	)
>=20
> >=20
>=20
> > ,(	2,	1400,	1500	)
>=20
> >=20
>=20
> > --	BETA-2
>=20
> >=20
>=20
> > ,(	3,	1001,	1889	)
>=20
> >=20
>=20
> > ,(	3,	1002,	1458	)
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > select M.MAIN_NAME
>=20
> >=20
>=20
> > ,	S.LOW_VAL
>=20
> >=20
>=20
> > ,	S.UP_VAL
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > from @MAIN_RECORD	M, @SUB_RECORD S
>=20
> >=20
>=20
> > where	M.ID =3D S.MAIN_ID
>=20
> >=20
>=20
> > and	M.MAIN_NAME like @MAIN_NAME
>=20
> >=20
>=20
> > and	@ATTR	between S.LOW_VAL and S.UP_VAL;
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > result is=20
>=20
> >=20
>=20
> > MAIN_NAME	LOW_VAL	UP_VAL
>=20
> >=20
>=20
> > BETA-0	100	200
>=20
> >=20
>=20
> > BETA-0	101	201
>=20
> >=20
>=20
> > and this is good result, only one parent record found.
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > If I say @ATTR =3D 1424
>=20
> >=20
>=20
> > result is
>=20
> >=20
>=20
> > MAIN_NAME	LOW_VAL	UP_VAL
>=20
> >=20
>=20
> > BETA-1	1400	1500
>=20
> >=20
>=20
> > BETA-2	1001	1889
>=20
> >=20
>=20
> > BETA-2	1002	1458
>=20
> >=20
>=20
> > and I'd like NOT to output anything in this case, as there are two pare=
nt records matching.
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > I can deal with this on the application side, where I would programmati=
cally count how many distinct MAIN_NAME came back and discard results when =
it is more than one.=20
>=20
> >=20
>=20
> >=20
>=20
> >=20
>=20
> > But I suspect there are ways to do it on the server side. Any thoughts =
would be appreciated.
>=20
>=20
>=20
> You could use:
>=20
>=20
>=20
> 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
>=20
> GROUP BY MAIN_NAME HAVING COUNT(MAIN_NAME) =3D 1

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