2 Derived tables within FROM clause

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

Transact-SQL (2000)

2 Derived tables within FROM clause

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-11-01 : 11:10:45

I have an SQL statement that parses and runs ok.

SELECT
xAE_MDS.AlcoholRelatedYN,
xAE_MDS.AEAttendanceNumber,
xAE_MDS.ArrivalDate,
xAE_InvTreatDiag.Diag1MnemonicDesc,
xAE_NatCodesLookup.DescName,
ctatt.CtofAtt
FROM
(
SELECT
ctatt.LocalPatientIdentifier,
COUNT(*) AS CtofAtt
FROM xAE_MDS ctatt
GROUP BY ctatt.LocalPatientIdentifier
)
ctatt
INNER JOIN xAE_MDS on
ctatt.LocalPatientIdentifier = xAE_MDS.LocalPatientIdentifier

INNER JOIN xAE_InvTreatDiag ON xAE_MDS.AEAttendanceNumber = xAE_InvTreatDiag.AEattNumber
INNER JOIN xAE_NatCodesLookup ON xAE_MDS.AEAttendanceDisposal = xAE_NatCodesLookup.Number

WHERE
(xAE_MDS.ArrivalDate BETWEEN CONVERT(DATETIME, '2006-04-01 00:00:00', 102)
AND CONVERT(DATETIME, '2007-03-31 00:00:00', 102))
AND (xAE_MDS.AlcoholRelatedYN = N'Y')
AND (xAE_NatCodesLookup.TableName = N'DISPOSAL')

---------------------------------------
I would like to add a 2nd derived table to the statement.
This is how i imagined the SQL could have been added....
---------------------------------------

SELECT
xAE_MDS.AlcoholRelatedYN,
xAE_MDS.AEAttendanceNumber,
xAE_MDS.ArrivalDate,
xAE_InvTreatDiag.Diag1MnemonicDesc,
xAE_NatCodesLookup.DescName,
ctatt.CtofAtt
FROM
(
SELECT
ctatt.LocalPatientIdentifier,
COUNT(*) AS CtofAtt
FROM xAE_MDS ctatt
GROUP BY ctatt.LocalPatientIdentifier
)
ctatt

-----------------------------------------------
--Position for 2nd derived table
INNER JOIN
(
SELECT
ctAlco.LocalPatientIdentifier,
COUNT(*) AS CtAlcoAtt
FROM xAE_MDS ctAlco
WHERE (ctAlco.FLAG = N'Y')
GROUP BY ctAlco.LocalPatientIdentifier) ctAlco INNER JOIN xAE_MDS ae2 ON
ctAlco.LocalPatientIdentifier = ae2.LocalPatientIdentifier
-----------------------------------------------

INNER JOIN xAE_MDS on
ctatt.LocalPatientIdentifier = xAE_MDS.LocalPatientIdentifier

INNER JOIN xAE_InvTreatDiag ON xAE_MDS.AEAttendanceNumber = xAE_InvTreatDiag.AEattNumber
INNER JOIN xAE_NatCodesLookup ON xAE_MDS.AEAttendanceDisposal = xAE_NatCodesLookup.Number

WHERE
(xAE_MDS.ArrivalDate BETWEEN CONVERT(DATETIME, '2006-04-01 00:00:00', 102)
AND CONVERT(DATETIME, '2007-03-31 00:00:00', 102))
AND (xAE_MDS.AlcoholRelatedYN = N'Y')
AND (xAE_NatCodesLookup.TableName = N'DISPOSAL')

----------------------------------------------
I’m pointed to incorrect syntax near WHERE
I’ve played around with the structure & can’t find a solution -
I would love to add the 2nd derived table can anyone help?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 11:18:38

the problem is here...

quote:

SELECT
xAE_MDS.AlcoholRelatedYN,
xAE_MDS.AEAttendanceNumber,
xAE_MDS.ArrivalDate,
xAE_InvTreatDiag.Diag1MnemonicDesc,
xAE_NatCodesLookup.DescName,
ctatt.CtofAtt
FROM
(
SELECT
ctatt.LocalPatientIdentifier,
COUNT(*) AS CtofAtt
FROM xAE_MDS ctatt
GROUP BY ctatt.LocalPatientIdentifier
)
ctatt

-----------------------------------------------
--Position for 2nd derived table
INNER JOIN
(
SELECT
ctAlco.LocalPatientIdentifier,
COUNT(*) AS CtAlcoAtt
FROM xAE_MDS ctAlco
WHERE (ctAlco.FLAG = N'Y')
GROUP BY ctAlco.LocalPatientIdentifier) ctAlco INNER JOIN xAE_MDS ae2 ON
ctAlco.LocalPatientIdentifier = ae2.LocalPatientIdentifier
-----------------------------------------------

INNER JOIN xAE_MDS on
ctatt.LocalPatientIdentifier = xAE_MDS.LocalPatientIdentifier

INNER JOIN xAE_InvTreatDiag ON xAE_MDS.AEAttendanceNumber = xAE_InvTreatDiag.AEattNumber
INNER JOIN xAE_NatCodesLookup ON xAE_MDS.AEAttendanceDisposal = xAE_NatCodesLookup.Number

WHERE
(xAE_MDS.ArrivalDate BETWEEN CONVERT(DATETIME, '2006-04-01 00:00:00', 102)
AND CONVERT(DATETIME, '2007-03-31 00:00:00', 102))
AND (xAE_MDS.AlcoholRelatedYN = N'Y')
AND (xAE_NatCodesLookup.TableName = N'DISPOSAL')

you've joined the two tables together but not joined them to anything else

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 11:23:33

did you perhaps mean...

quote:

SELECT
xAE_MDS.AlcoholRelatedYN,
xAE_MDS.AEAttendanceNumber,
xAE_MDS.ArrivalDate,
xAE_InvTreatDiag.Diag1MnemonicDesc,
xAE_NatCodesLookup.DescName,
ctatt.CtofAtt
FROM
(
SELECT
ctatt.LocalPatientIdentifier,
COUNT(*) AS CtofAtt
FROM xAE_MDS ctatt
GROUP BY ctatt.LocalPatientIdentifier
)
ctatt

INNER JOIN xAE_MDS on
ctatt.LocalPatientIdentifier = xAE_MDS.LocalPatientIdentifier
-----------------------------------------------
--Position for 2nd derived table
INNER JOIN
(
SELECT
ctAlco.LocalPatientIdentifier,
COUNT(*) AS CtAlcoAtt
FROM xAE_MDS ctAlco
WHERE (ctAlco.FLAG = N'Y')
GROUP BY ctAlco.LocalPatientIdentifier) ctAlco ON
ctAlco.LocalPatientIdentifier = xAE_MDS.LocalPatientIdentifier
-----------------------------------------------

INNER JOIN xAE_InvTreatDiag ON xAE_MDS.AEAttendanceNumber = xAE_InvTreatDiag.AEattNumber
INNER JOIN xAE_NatCodesLookup ON xAE_MDS.AEAttendanceDisposal = xAE_NatCodesLookup.Number

WHERE
(xAE_MDS.ArrivalDate BETWEEN CONVERT(DATETIME, '2006-04-01 00:00:00', 102)
AND CONVERT(DATETIME, '2007-03-31 00:00:00', 102))
AND (xAE_MDS.AlcoholRelatedYN = N'Y')
AND (xAE_NatCodesLookup.TableName = N'DISPOSAL')

i.e. introduce the xAE_MDS table first then join to it

Em

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-11-01 : 11:24:47

sorry EM i pasted the 2nd derived table incorrectly..
here is where i imagined it would live.
I believe i have joined the 2 tables - can you explain with corrected code (sorry)

--------------------------------------
SELECT
xAE_MDS.AlcoholRelatedYN,
xAE_MDS.AEAttendanceNumber,
xAE_MDS.ArrivalDate,
xAE_InvTreatDiag.Diag1MnemonicDesc,
xAE_NatCodesLookup.DescName,
ctatt.CtofAtt
FROM
(
SELECT
ctatt.LocalPatientIdentifier,
COUNT(*) AS CtofAtt
FROM xAE_MDS ctatt
GROUP BY ctatt.LocalPatientIdentifier
)
ctatt
INNER JOIN xAE_MDS on
ctatt.LocalPatientIdentifier = xAE_MDS.LocalPatientIdentifier

-----------------------------------------------
--Position for 2nd derived table
INNER JOIN
(
SELECT
ctAlco.LocalPatientIdentifier,
COUNT(*) AS CtAlcoAtt
FROM xAE_MDS ctAlco
WHERE (ctAlco.FLAG = N'Y')
GROUP BY ctAlco.LocalPatientIdentifier) ctAlco INNER JOIN xAE_MDS ae2 ON
ctAlco.LocalPatientIdentifier = ae2.LocalPatientIdentifier
-----------------------------------------------

INNER JOIN xAE_InvTreatDiag ON xAE_MDS.AEAttendanceNumber = xAE_InvTreatDiag.AEattNumber
INNER JOIN xAE_NatCodesLookup ON xAE_MDS.AEAttendanceDisposal = xAE_NatCodesLookup.Number

WHERE
(xAE_MDS.ArrivalDate BETWEEN CONVERT(DATETIME, '2006-04-01 00:00:00', 102)
AND CONVERT(DATETIME, '2007-03-31 00:00:00', 102))
AND (xAE_MDS.AlcoholRelatedYN = N'Y')
AND (xAE_NatCodesLookup.TableName = N'DISPOSAL')

--------------

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 11:29:37

ok, to show you what i mean... below is you code but with the derived table definition excluded


FROM ctatt
		INNER JOIN ctAlco 
		INNER JOIN xAE_MDS ae2 ON ctAlco.LocalPatientIdentifier = ae2.LocalPatientIdentifier
		INNER JOIN xAE_MDS on ctatt.LocalPatientIdentifier = xAE_MDS.LocalPatientIdentifier 
		INNER JOIN xAE_InvTreatDiag ON xAE_MDS.AEAttendanceNumber = xAE_InvTreatDiag.AEattNumber 
		INNER JOIN xAE_NatCodesLookup ON xAE_MDS.AEAttendanceDisposal = xAE_NatCodesLookup.Number

it's clearer to see now that you've not defined what you joining your second table on. make sense?

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 11:46:37

sorry. just spotted the difference in the code you posted. the issue is the same though. you've done...


FROM ctatt 
INNER JOIN xAE_MDS on ctatt.LocalPatientIdentifier = xAE_MDS.LocalPatientIdentifier 
INNER JOIN ctAlco 
INNER JOIN xAE_MDS ae2 ON ctAlco.LocalPatientIdentifier = ae2.LocalPatientIdentifier 
INNER JOIN xAE_InvTreatDiag ON xAE_MDS.AEAttendanceNumber = xAE_InvTreatDiag.AEattNumber 
INNER JOIN xAE_NatCodesLookup ON xAE_MDS.AEAttendanceDisposal = xAE_NatCodesLookup.Number

they still join to each other but not to anything else in the query.
if you want a another version of xAE_MDS how does that relate to the rest of the tables?
or is it as i posted above... that you can just join to the first instance of that table?

Em

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-11-01 : 12:08:52

Em, that was an excellent way of showing me how I’d gone wrong!
Thanks to your help I have corrected the SQL statement – and learnt something new.
Brilliant!
Thanks Dan.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 15:58:57

no prob

lots of derived tables can make it messy to debug, i often end up laying it out like that to check my logic

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources