join 2 complex queries to 1

**Simon Hayes** · Jul 20 '05, 04:16 AM

Re: join 2 complex queries to 1

"Beachvolleybal ler" <wl93@gmx.de> wrote in message
news:e9ecb84e.0 407190614.53d27 560@posting.goo gle.com...[color=blue]
> hi there
>
> anyone had an idea to join following 2 queries to 1????
>
>
> ----- QUERY 1 ---------------------------------------------
> SELECT TMS_CaseF_2.Nam e AS TCDomain_0,
> TMS_CaseF_3.Nam e AS TCDomain_1,
> TMS.CaseF.Name AS TCFolder_2,
> TMS_CaseF_1.Nam e AS TCFolder_3,
> TMS.TestCase.Na me AS TCName_4,
> TMS_TestCase_1. Name AS TCName_5,
> TMS.LogFolder.N ame AS PlannedLogFolde r_6,
> TMS.Log.Name AS PlannedLog_7,
> TMS.CaseResult. RecordedCaseNam e AS TCRecordedName_ 8,
> TMS.TestPlan.Na me AS Plan_9
> FROM
> (
> (
> (
> (
> (
> (
> (
> (
> (
> (TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =
> TMS.LogFolder.B uild)
> INNER JOIN TMS.Log ON TMS.LogFolder.U ID = TMS.Log.LogFold er
> )
> INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult. Log
> )
> INNER JOIN TMS.TestCase ON TMS.CaseResult. TestCase =
> TMS.TestCase.UI D
> )
> LEFT JOIN TMS.CaseF ON TMS.TestCase.Pa rent = TMS.CaseF.UID)
> LEFT JOIN TMS.TestCase AS TMS_TestCase_1 ON TMS.TestCase.Pa rent =
> TMS_TestCase_1. UID
> )
> LEFT JOIN TMS.CaseF AS TMS_CaseF_1 ON TMS_TestCase_1. Parent =
> TMS_CaseF_1.UID
> )
> LEFT JOIN TMS.CaseF AS TMS_CaseF_2 ON TMS_CaseF_1.Par ent =
> TMS_CaseF_2.UID
> )
> LEFT JOIN TMS.CaseF AS TMS_CaseF_3 ON TMS.CaseF.Paren t =
> TMS_CaseF_3.UID
> )
> INNER JOIN TMS.TestPlan ON TMS.TestCase.Te stPlan = TMS.TestPlan.UI D
> )
> WHERE (((TMS.LogFolde r.Name) Like 'TR1%')
> AND ((TMS.Build.Nam e)='Planning_VD 10A'))
> ORDER BY TMS.CaseF.Name,
> TMS_CaseF_1.Nam e,
> TMS.TestCase.Na me,
> TMS_TestCase_1. Name;
> -------------------------------------------------------------
>
>
>
>
> ----- QUERY 2 ---------------------------------------------
> SELECT TMS.CaseResult. RecordedCaseNam e
> FROM ((TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =
> TMS.LogFolder.B uild)
> INNER JOIN TMS.Log ON TMS.LogFolder.U ID = TMS.Log.LogFold er)
> INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult. Log
> WHERE (((TMS.LogFolde r.Name) Like 'VD%')
> AND ((TMS.Build.Nam e)='VD10A IT_APP'));[/color]

I'm not entirely sure what you want to do, but you may be looking for either
a UNION or a derived table join:

select col1 as 'Column1'
from dbo.table1
union all
select col1
from dbo.table2

Note that UNION on its own will remove duplicate rows, but this takes extra
processing, so only use it if you know it's necessary, otherwise stick with
UNION ALL.

This is a derived table join:

select dt1.col1, dt1.col2, dt2.col2
from
(
select col1, col2
from dbo.table1
) dt1
join
(
select col1, col2
from dbo.table2
) dt2
on dt1.col1 = dt2.col1

If that's not helpful, perhaps you can clarify what you mean by 'joining'
two queries to one.

Simon

join 2 complex queries to 1

join 2 complex queries to 1

Comment