Received: by 10.66.72.134 with SMTP id d6mr1768226pav.20.1355519760182; Fri, 14 Dec 2012 13:16:00 -0800 (PST)
Received: by 10.50.34.167 with SMTP id a7mr1192540igj.5.1355519760142; Fri, 14 Dec 2012 13:16:00 -0800 (PST)
Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!feeder.erje.net!us.feeder.erje.net!news.snarked.org!newsfeed.news.ucla.edu!usenet.stanford.edu!kt20no3470887pbb.1!news-out.google.com!6ni44385pbd.1!nntp.google.com!kt20no3470883pbb.1!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-sqlserver
Date: Fri, 14 Dec 2012 13:15:59 -0800 (PST)
In-Reply-To: <XnsA120E32BDA968Yazorman@127.0.0.1>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=216.14.58.122; posting-account=PG2dbQkAAADVILsQ0GhgAM6hZK18SIjs
NNTP-Posting-Host: 216.14.58.122
References: <793b9352-0831-43e1-b48d-bf993714ba21@googlegroups.com> <7fe453d0-4aec-41e0-91bd-77a5d3e08792@googlegroups.com> <00bf8bb5-dafc-426f-b228-e62ba1fbfa28@googlegroups.com> <XnsA120E32BDA968Yazorman@127.0.0.1>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <ba1d2125-474f-4f55-9be5-325a8200d44f@googlegroups.com>
Subject: Re: join with one parent record only
From: migurus <migurus@yahoo.com>
Injection-Date: Fri, 14 Dec 2012 21:16:00 +0000
Content-Type: text/plain; charset=ISO-8859-1
Xref: csiph.com comp.databases.ms-sqlserver:1370

On Wednesday, December 5, 2012 1:19:54 PM UTC-8, Erland Sommarskog wrote:
> migurus (migurus@yahoo.com) writes:
> 
> > Thanks, but I can not use this approach, as application needs both child
> 
> > records if there are two of them 
> 
> 
> 
> This seems to give you what you want:
> 
> 
> 
> ; WITH CTE AS (
> 
>    select M.MAIN_NAME
> 
>    ,	S.LOW_VAL
> 
>    ,	S.UP_VAL, COUNT(*) OVER (PARTITION BY M.ID) AS parent_count,
> 
>    COUNT(*) OVER() AS total_count
> 
> 
> 
>    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
> 
> )
> 
> SELECT MAIN_NAME, LOW_VAL, UP_VAL
> 
> FROM   CTE
> 
> WHERE  parent_count = total_count
> 
> 
> 
> 
> 
> 
> 
> -- 
> 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> 
> 
> Links for SQL Server Books Online:
> 
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> 
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Thank you very much, it does work. I need to learn CTE technique.