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

Re: join with one parent record only

Path	csiph.com!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: join with one parent record only
Date	Wed, 05 Dec 2012 22:19:54 +0100
Organization	Erland Sommarskog
Lines	30
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>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx04.eternal-september.org; posting-host="102f3b4a812ef50a29978963a1d5bdd7"; logging-data="22501"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19hyzZZA7TtASeSHj7u99+E"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:8OWt0KmmBCYyjSPrUkUi5J0vxB0=
Xref	csiph.com comp.databases.ms-sqlserver:1361

Show key headers only | 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