There has gotta be a better way

[Tom Loach]

I ended up writing the folling code to get a query to work:

create table #PI (prot_inv_id int, inv_type int, protocol_ID int)
insert into #PI (prot_inv_id, inv_type, protocol_id)
(select Prot_inv_ID, inv_type, protocol_ID
from Protocol_investigator where inv_type in (1,2))

SELECT distinct Protocols.Protocol_ID, Protocols.HICNumber,
Protocols.title,
(select Prot_inv_ID from #PI where Inv_Type = 1
and #PI.Protocol_ID = Protocols.Protocol_ID) as Investigator,
(select Prot_inv_ID from #PI Where Inv_Type = 2
and #PI.Protocol_ID = Protocols.Protocol_ID) as Coordinator,
Protocols.Active
FROM dbo.Protocols
LEFT OUTER JOIN #PI
ON dbo.Protocols.protocol_id = #PI.protocol_id

But I'm sure there is a way to do this without first building a temp
table. When I tried to put a where clause in the main body of the SQL
I would, despite the outer join, end up getting only those records
from both tables that matched. That is to say when I moved the
select Prot_inv_ID, inv_type, protocol_ID
from Protocol_investigator where inv_type in (1,2))
as a where clause I came up short.

Does anyone know how to essentially include a subselect to an outer
join?

Thanks,
Tom

[Steve Jorgensen]

On Fri, 16 Jan 2004 11:10:43 -0500, Tom Loach <tc***@virginia.edu> wrote:

I ended up writing the folling code to get a query to work:

create table #PI (prot_inv_id int, inv_type int, protocol_ID int)
insert into #PI (prot_inv_id, inv_type, protocol_id)
(select Prot_inv_ID, inv_type, protocol_ID
from Protocol_investigator where inv_type in (1,2))

SELECT distinct Protocols.Protocol_ID, Protocols.HICNumber,
Protocols.title,
(select Prot_inv_ID from #PI where Inv_Type = 1
and #PI.Protocol_ID = Protocols.Protocol_ID) as Investigator,
(select Prot_inv_ID from #PI Where Inv_Type = 2
and #PI.Protocol_ID = Protocols.Protocol_ID) as Coordinator,
Protocols.Active
FROM dbo.Protocols
LEFT OUTER JOIN #PI
ON dbo.Protocols.protocol_id = #PI.protocol_id

But I'm sure there is a way to do this without first building a temp
table. When I tried to put a where clause in the main body of the SQL
I would, despite the outer join, end up getting only those records
from both tables that matched. That is to say when I moved the
select Prot_inv_ID, inv_type, protocol_ID
from Protocol_investigator where inv_type in (1,2))
as a where clause I came up short.

Does anyone know how to essentially include a subselect to an outer
join?

Thanks,
Tom

What you need to do is include the filter criteria in the join ON expression.

[John Bell]

Hi

Why not (untested) :

SELECT distinct P.Protocol_ID, P.HICNumber,
P.title,
CASE when PI.Inv_Type = 1 THEN PI.Prot_inv_ID END as Investigator,
CASE when PI.Inv_Type = 1 THEN PI.Prot_inv_ID END as Coordinator,
P.Active
FROM dbo.Protocols P
LEFT OUTER JOIN Protocol_investigator PI ON P.protocol_id = PI.protocol_id
AND PI.inv_type in (1,2)

John

"Tom Loach" <tc***@virginia.edu> wrote in message
news:mv********************************@4ax.com...

I ended up writing the folling code to get a query to work:

create table #PI (prot_inv_id int, inv_type int, protocol_ID int)
insert into #PI (prot_inv_id, inv_type, protocol_id)
(select Prot_inv_ID, inv_type, protocol_ID
from Protocol_investigator where inv_type in (1,2))

SELECT distinct Protocols.Protocol_ID, Protocols.HICNumber,
Protocols.title,
(select Prot_inv_ID from #PI where Inv_Type = 1
and #PI.Protocol_ID = Protocols.Protocol_ID) as Investigator,
(select Prot_inv_ID from #PI Where Inv_Type = 2
and #PI.Protocol_ID = Protocols.Protocol_ID) as Coordinator,
Protocols.Active
FROM dbo.Protocols
LEFT OUTER JOIN #PI
ON dbo.Protocols.protocol_id = #PI.protocol_id

But I'm sure there is a way to do this without first building a temp
table. When I tried to put a where clause in the main body of the SQL
I would, despite the outer join, end up getting only those records
from both tables that matched. That is to say when I moved the
select Prot_inv_ID, inv_type, protocol_ID
from Protocol_investigator where inv_type in (1,2))
as a where clause I came up short.

Does anyone know how to essentially include a subselect to an outer
join?

Thanks,
Tom