merge query into Join as table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

merge query into Join as table?

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-04-27 : 19:49:04

I am trying to join a value from 1 table to another... the question I have is mystats is really a fairly large query.

select linenumber from CustomerLineNumber, customer.mystats, proc_fkey.mystats. inTotal.mystats

from CustomerLineNumber, mystats

where customerLineNumber.customer = mystats.customer

But myStats looks like this:

SELECT CUSTOMER, PROC_FKey, COUNT(BoolCtr) AS inTotal
FROM (SELECT CSB_FKey, PROC_FKey, LEFT(CSB_FKey, 5) AS CUSTOMER, PassStatus, 1 AS BoolCtr
FROM dbo.TestDynamic
WHERE (PassStatus <> 'PASS') AND (TestTime >= '20060121') AND (TestTime < '20060221')) AS myStats
GROUP BY CUSTOMER, PROC_FKey

What changes in the first join query so that it includes the result query table ?

The following did not WORK :

select linenumber from CustomerLineNumber, customer.mystats, proc_fkey.mystats. inTotal.mystats

from CustomerLineNumber, ( SELECT CUSTOMER, PROC_FKey, COUNT(BoolCtr) AS inTotal
FROM (SELECT CSB_FKey, PROC_FKey, LEFT(CSB_FKey, 5) AS CUSTOMER, PassStatus, 1 AS BoolCtr
FROM dbo.TestDynamic
WHERE (PassStatus <> 'PASS') AND (TestTime >= '20060121') AND (TestTime < '20060221')) AS myStats
GROUP BY CUSTOMER, PROC_FKey)

where customerLineNumber.customer = mystats.customer

andrewcw

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-27 : 19:53:35

Your blue query is a bit unclear, but this should get you started. You have two derived tables in this query. Each derived table must have an alias even if you don't use it. The inner derived table I aliased as t. t isn't used anywhere else.


select c.linenumber, mystats.customer, mystats.proc_fkey, mystats.intotal
from CustomerLineNumber c
inner join 
(
	SELECT CUSTOMER, PROC_FKey, COUNT(BoolCtr) AS inTotal
	FROM 
	(
		SELECT CSB_FKey, PROC_FKey, LEFT(CSB_FKey, 5) AS CUSTOMER, PassStatus, 1 AS BoolCtr
		FROM dbo.TestDynamic
		WHERE (PassStatus <> 'PASS') AND (TestTime >= '20060121') AND (TestTime < '20060221')
	) t
	GROUP BY CUSTOMER, PROC_FKey
) mystats
on c.customer = mystats.customer

Tara Kizer
aka tduggan

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-04-27 : 20:06:56

Wow - I am amazed - thanks for your help !!

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources