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

Re: join with one parent record only

Newsgroups	comp.databases.ms-sqlserver
Date	2012-12-14 13:15 -0800
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>
Message-ID	<ba1d2125-474f-4f55-9be5-325a8200d44f@googlegroups.com> (permalink)
Subject	Re: join with one parent record only
From	migurus <migurus@yahoo.com>

Show all headers | View raw

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.

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