Joining Results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining Results

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-06-18 : 03:31:05

How do i join the results of following CTE'S

With MySideY(Cust,introducer,side) as
(
select custid,introducer_id,side from tbl_customers where introducer_id=203344 and side='Y'
union all
select T.custid,T.introducer_id,ts.side from tbl_customers T join MySideY TS on T.introducer_id=Ts.cust
)
select count(introducer),min(introducer) from MySideY group by side

;With MySideX(Cust,introducer,side) as
(
select custid,introducer_id, side from tbl_customers where introducer_id=203344 and side='X'
union all
select T.custid,T.introducer_id, TS.side from tbl_customers T join MySideX TS on T.introducer_id=Ts.cust
)
select count(introducer),min(introducer) from MySideX group by side

Above Cte's give me exact results but how i join results like this..l

Custid Sidex sideY
xxx 67 7

Thanks in Advance

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-18 : 04:57:31

Hi,

Insert the output of cte's into two table variables and join those table variables and get what you want..

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-18 : 04:58:39

My 1000th post!

I thought this would be trivially easy but maybe not....

the basic outline is you would chain the CTE like this


WITH xxx AS ()
, yyy AS ()
SELECT ......

However I'm not so sure about your data. I came up with this (you might not need the full outer join... My CTE's are trivially simple but the same principal should apply You can run to test output.


DECLARE @foo_X TABLE (
	[Id] INT
	, [Comp] INT
	)

DECLARE @foo_Y TABLE (
	[Id] INT
	, [Comp] INT
	)

INSERT @foo_X ([Id], [Comp])
          SELECT 1, 10
UNION ALL SELECT 1, 23
UNION ALL SELECT 2, 30
UNION ALL SELECT 3, 20
UNION ALL SELECT 3, 20

INSERT @foo_Y([Id], [Comp])
          SELECT 1, 123
UNION ALL SELECT 2, 2123
UNION ALL SELECT 2, 323
UNION ALL SELECT 2, -10
UNION ALL SELECT 5, 10
UNION ALL SELECT 6, 20

;WITH tabX ([Id], [Comp], [axis]) AS (
	SELECT [Id], [Comp], 'x'
	FROM @foo_X
	)
, tabY([Id], [Comp], [axis]) AS (
	SELECT [Id], [Comp], 'y'
	FROM @foo_Y
	)
SELECT
	[ID]
	, COUNT(CASE [axis] WHEN 'x' THEN [comp] ELSE NULL END) AS [xCount]
	, MIN(CASE [axis] WHEN 'x' THEN [comp] ELSE NULL END) AS [xMin]
	, COUNT(CASE [axis] WHEN 'y' THEN [comp] ELSE NULL END) AS [yCount]
	, MIN(CASE [axis] WHEN 'y' THEN [comp] ELSE NULL END) AS [yMin]
FROM
	(
	SELECT
		x.[ID] AS [Id]
		, [comp] AS [comp]
		, [axis] AS [axis]
	FROM
		tabX x

	UNION ALL SELECT
		y.[ID] AS [Id]
		, [comp] AS [comp]
		, [axis] AS [axis]
	FROM
		tabY y
	)
	Combined
GROUP BY
	[Id]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-06-18 : 05:46:25

Thanks to you both will revert u back after try your suggestions...
Thanks a Ton...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-06-18 : 05:55:12

well i do this like that... But i personally dont like that way...

;With MySide(Cust,introducer,side) as
(
select custid,introducer_id,Side=case when side='X' then 'X' else 'Y' end from tbl_customers where introducer_id=203344
union all
select T.custid,T.introducer_id,ts.side from tbl_customers T join MySide TS on T.introducer_id=Ts.cust
)
select 'Side X' = sum(case when TS.Side = 'X' then 1 else 0 end),
'Side Y' = Sum(case when TS.Side = 'Y' then 1 else 0 end),
min(TS.introducer) as Customer
from MySide TS Join Tbl_Customers TC on TS.introducer=TC.custid
OPTION (MAXRECURSION 1000)

so any alternative to this.... Any other help

Thanks in Advance...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources