Problem structuring SQL for combining 3 tables

[TheQuestioner]

Hi

I'm having a problem trying to create a SQL statement. I have 3 tables which are composed of:-

tblDirectors

ID	Name
----------------
1	Gorden
2	Alyx
3	Eli

tblDirectorOfficeReferences

ID IDDirector IDOffice Ref
-------------------------------
1  2	      1	       Test 156
2  2          2        Test 562
3  1          5        Test 099
4  3          1        Test 984
5  3          7        Test 125
6  3          5        Test 456

tblOffices

ID	Name	Type
---------------------
1	Alpha	 CT
2	Beta 	 CT
3	Charlie  CT
4	Delta	 PA
5	Echo	 PA
6	Foxtrot	 PA
7	Golf	 VA
8	Hotel	 VA
9	India	 VA

I want to create the folloiwng results:-

DirectorID DirectorName CTOffice PAOffice VAOffice
---------------------------------------------------
1	   Gorden	No	Yes	  No
2	   Alyx		Yes	No	  No
3	   Eli		Yes     Yes	  Yes

I want to return whether each director has an occurance of an office with type CT, PA or VA within tblOffices. Any help would be greatly appreciated.

 

[gmmastros]

If these are the only states you need to worry about, then the following query should work pretty well.

Select 	tblDirectors.Id As DirectorId,
		tblDirectors.Name As DirectorName,
		Case When CT.Type Is NULL Then 'No' Else 'Yes' End As CTOffice,
		Case When PA.Type Is NULL Then 'No' Else 'Yes' End As PAOffice,
		Case When VA.Type Is NULL Then 'No' Else 'Yes' End As VAOffice
From	tblDirectors
		Left Join 	(
					Select	Distinct
							IDDirector, Type
					From	tblDirectorOfficeReferences
							Inner Join tblOffices On tblDirectorOfficeReferences.IDOffice = tblOffices.ID
					Where	Type = 'CT'
				  	) CT On tblDirectors.Id = CT.IDDirector
		Left Join 	(
					Select	Distinct
							IDDirector, Type
					From	tblDirectorOfficeReferences
							Inner Join tblOffices On tblDirectorOfficeReferences.IDOffice = tblOffices.ID
					Where	Type = 'PA'
					) PA On tblDirectors.Id = PA.IDDirector
		Left Join	(
					Select	Distinct
							IDDirector, Type
					From	tblDirectorOfficeReferences
							Inner Join tblOffices On tblDirectorOfficeReferences.IDOffice = tblOffices.ID
					Where	Type = 'VA'
					) VA On tblDirectors.Id = VA.IDDirector

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TheQuestioner]

Thanks George, it worked a treat!

 

[gmmastros]

Glad to help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Slightly different query (one-pass, no multiple joins):

select D.ID as DirectorID, D.Name as DirectorName,
	case when X.CT > 0 then 'Yes' else 'No' end as CTOffice,
	case when X.PA > 0 then 'Yes' else 'No' end as PAOffice,
	case when X.VA > 0 then 'Yes' else 'No' end as VAOffice
from tblDirectors D
left outer join
(	select DOR.IDDirector, 
		sum(case when O.Type='CT' then 1 else 0 end) as CT,
		sum(case when O.Type='PA' then 1 else 0 end) as PA,
		sum(case when O.Type='VA' then 1 else 0 end) as VA
	from tblDirectorOfficeReferences DOR
	inner join tblOffices O on O.ID=DOR.IDOffice
	group by DOR.IDDirector
) X on D.ID=X.IDDirector
order by D.ID

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

TheQuestioner,

Whenever you are presented an oppurtunity to use one of my queries, or one supplied by vongrunt, my advice would be to use his. His are usually better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TheQuestioner]

Thank you to both George and Vongrunt: your both too modest ;-)