Complicated Join - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Complicated Join

Author

Topic

Nihar
Starting Member

3 Posts

Posted - 2006-11-15 : 14:39:26

Hi All,

I am trying to get a result set like this

Code Facility1 Facility2
----- --------- ---------
abc 1 [null]
def [null] 5
ekg [null] 1

The data to be retreived and compared is stored in three tables

Here is the data

ZrEc table
----------
SID ctasCd
1000000 3
1000003 2
1000004 2
1000007 2
1000002 2
1000028 2
1000029 1
1000030 1

Serv Table
----------
SID encntrSID
1000000 2000001
1000003 2000002
1000004 2000003
1000007 2000004
1000002 1000001
1000028 1000027
1000029 5000021
1000030 5000023

LocTrackHist table
------------------

SID currFacID
2000001 TOH-CC
1000001 TOH-CC
2000003 TOH-CC
2000002 TOH-CC
1000027 TOH-CC
2000004 TOH-CC
5000021 TOH-CC
5000023 TOH-GC

Here is the SQL that i have been working with, but no result

select C.codeDesc,
count(Zr1.SID) "TOH-CC",
count (Zr2.SID) "TOH-GC"
from ctasCd C, ZrEc Zr1, Serv S1, Serv EnServ1, LocTrackHist LTH1, Serv S2, Serv EnServ2, LocTrackHist LTH2, ZrEc Zr2
where LTH1.currFacID = 'TOH-CC'
and EnServ1.SID = LTH1.SID
and S1.encntrSID = EnServ1.SID
and C.code = Zr1.ctasCd
and S1.SID not in
(select SID from Serv where encntrSID in(select SID from Serv where servTypeCd = 'En' and resultStsCd = 'eie') and TID = 75)
and S1.SID = Zr1.SID
and S1.startDtm >= '09/13/2006'
and S1.startDtm <= '11/16/2006'
and LTH2.currFacID = 'TOH-GC'
and EnServ2.SID = LTH2.SID
and S2.encntrSID = EnServ2.SID
and C.code = Zr2.ctasCd
and S2.SID not in
(select SID from Serv where encntrSID in(select SID from Serv where servTypeCd = 'En' and resultStsCd = 'eie') and TID = 75)
and S2.SID = Zr2.SID
and S2.startDtm >= '09/13/2006'
and S2.startDtm <= '11/16/2006'
and Zr1.SID = Zr2.SID
group by C.code
order by C.code

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 14:48:16

Where is ctasCd table?

Peter Larsson
Helsingborg, Sweden

Nihar
Starting Member

3 Posts

Posted - 2006-11-15 : 14:54:16

ctasCd
------

codeTable code codeDesc siteDefFlg subTable
---------- ---- -------- ------------ ---------
ctasCd 1 Level 1 - Resuscitation 0 [NULL]
ctasCd 2 Level 2 - Emergent 0 [NULL]
ctasCd 3 Level 3 - Urgent 0 [NULL]
ctasCd 4 Level 4 - Less Urgent 0 [NULL]
ctasCd 5 Level 5 - Non Urgent 0 [NULL]

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-16 : 02:28:26

You dont really make it easy to help you, the expected output has little to do with the given data and the query uses unspecified coloumns and deprecated JOIN notaion.

But despite all that, is this by any chance what you were trying toachive?

SELECT C.codeDesc
	  ,COUNT(CASE WHEN LTH.currFacID = 'TOH-CC' THEN Zr.SID END) AS [TOH-CC]
	  ,COUNT(CASE WHEN LTH.currFacID = 'TOH-GC' THEN Zr.SID END) AS [TOH-GC]
FROM ctasCd C
LEFT OUTER JOIN ZrEc Zr
ON Zr.ctasCd = C.code
LEFT OUTER JOIN Serv S
ON S.SID = Zr.SID
LEFT OUTER JOIN Serv EnServ
ON EnServ.SID = S.encntrSID
LEFT OUTER JOIN LocTrackHist LTH
ON LTH.SID = EnServ.SID
WHERE S.SID not in 
	(SELECT SID from Serv where encntrSID in (SELECT SID FROM Serv WHERE servTypeCd = 'En' and resultStsCd = 'eie') AND TID = 75) 
	and S.startDtm >= '20060913' and S.startDtm <= '20061116'
GROUP BY C.codeDesc
ORDER BY C.codeDesc

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

Nihar
Starting Member

3 Posts

Posted - 2006-11-17 : 09:39:19

Thanks for your update, shall try it out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources