Received: by 10.224.207.66 with SMTP id fx2mr7200150qab.7.1354603802288; Mon, 03 Dec 2012 22:50:02 -0800 (PST)
Received: by 10.49.71.169 with SMTP id w9mr2801631qeu.7.1354603802260; Mon, 03 Dec 2012 22:50:02 -0800 (PST)
Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!c8no176698qao.0!news-out.google.com!gf5ni47564750qab.0!nntp.google.com!c8no180997qao.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-sqlserver
Date: Mon, 3 Dec 2012 22:50:02 -0800 (PST)
In-Reply-To: <793b9352-0831-43e1-b48d-bf993714ba21@googlegroups.com>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=95.16.117.206; posting-account=jIl9nQoAAAA9P37IIqQq64yAx-2NQYiu
NNTP-Posting-Host: 95.16.117.206
References: <793b9352-0831-43e1-b48d-bf993714ba21@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <7fe453d0-4aec-41e0-91bd-77a5d3e08792@googlegroups.com>
Subject: Re: join with one parent record only
From: bradbury9 <ray.bradbury9@gmail.com>
Injection-Date: Tue, 04 Dec 2012 06:50:02 +0000
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Xref: csiph.com comp.databases.ms-sqlserver:1359

El martes, 4 de diciembre de 2012 00:42:39 UTC+1, migurus  escribi=F3:
> 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 bot=
h tables. There are one or two child records per each parent.
>=20
>=20
>=20
> Running on SQL 2008, here is illustration, I omit key etc for brevity:
>=20
>=20
>=20
> declare	@MAIN_NAME	varchar(32) =3D 'B%';
>=20
> declare	@ATTR	int	=3D 124;
>=20
>=20
>=20
> declare	@MAIN_RECORD	table	(
>=20
> 	MAIN_NAME	varchar(32)	not null=09
>=20
> ,	ID		int	not null
>=20
> );
>=20
>=20
>=20
> declare	@SUB_RECORD		table	(
>=20
> 	MAIN_ID		int	not null
>=20
> ,	LOW_VAL		int	not null
>=20
> ,	UP_VAL		int	not null
>=20
>=20
>=20
> );
>=20
>=20
>=20
> insert into @MAIN_RECORD	values
>=20
>  ('BETA-0'	,1)
>=20
> ,('BETA-1'	,2)
>=20
> ,('BETA-2'	,3);
>=20
>=20
>=20
> insert into @SUB_RECORD		values
>=20
> --	BETA-0
>=20
>  (	1,	100,	200	)
>=20
> ,(	1,	101,	201	)
>=20
> --	BETA-1
>=20
> ,(	2,	1000,	1240	)
>=20
> ,(	2,	1001,	1251	)
>=20
> ,(	2,	1400,	1500	)
>=20
> --	BETA-2
>=20
> ,(	3,	1001,	1889	)
>=20
> ,(	3,	1002,	1458	)
>=20
>=20
>=20
>=20
>=20
>=20
>=20
> select M.MAIN_NAME
>=20
> ,	S.LOW_VAL
>=20
> ,	S.UP_VAL
>=20
>=20
>=20
> from @MAIN_RECORD	M, @SUB_RECORD S
>=20
> where	M.ID =3D S.MAIN_ID
>=20
> and	M.MAIN_NAME like @MAIN_NAME
>=20
> and	@ATTR	between S.LOW_VAL and S.UP_VAL;
>=20
>=20
>=20
> result is=20
>=20
> MAIN_NAME	LOW_VAL	UP_VAL
>=20
> BETA-0	100	200
>=20
> BETA-0	101	201
>=20
> and this is good result, only one parent record found.
>=20
>=20
>=20
> If I say @ATTR =3D 1424
>=20
> result is
>=20
> MAIN_NAME	LOW_VAL	UP_VAL
>=20
> BETA-1	1400	1500
>=20
> BETA-2	1001	1889
>=20
> BETA-2	1002	1458
>=20
> and I'd like NOT to output anything in this case, as there are two parent=
 records matching.
>=20
>=20
>=20
> I can deal with this on the application side, where I would programmatica=
lly count how many distinct MAIN_NAME came back and discard results when it=
 is more than one.=20
>=20
>=20
>=20
> But I suspect there are ways to do it on the server side. Any thoughts wo=
uld be appreciated.

You could use:

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