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

Re: join with one parent record only

From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: join with one parent record only
Date	2012-12-05 22:19 +0100
Organization	Erland Sommarskog
Message-ID	<XnsA120E32BDA968Yazorman@127.0.0.1> (permalink)
References	<793b9352-0831-43e1-b48d-bf993714ba21@googlegroups.com> <7fe453d0-4aec-41e0-91bd-77a5d3e08792@googlegroups.com> <00bf8bb5-dafc-426f-b228-e62ba1fbfa28@googlegroups.com>

Show all headers | View raw

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

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