Outer Join Error

[ncedddf]

when attempting to query three tables with this from and where clause,
FROM (select visitid,Diag_Code from tblInjury inj, luICD9 icd WHERE icd.Diag_Code *= inj.Injury_Code and icd.Diag_Code like 'E%')i, tblVisit v
WHERE i.visitid = v.visitid
I am receiving the following error.
'The table 'tblInjury' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.'
Is there a way around this?

 

[Bygbobbo]

Yes, there is but first please post the whole SQL statement.

This way I can convert the statement into ANSI standard SQL.

Thanks,
Bygs

 

[ncedddf]

The whole statement is huge. It has a very large case statement which in turn has a very large group by statement.

 

[ncedddf]

Here is a shortened version

SELECT case when Diag_Code = 'BLAH' then 'BLAH BLAH'
when Diag_Code = 'BAM' then 'BAM BAM'
else 'Unknow' END as Cluster,
COUNT(i.VisitID) AS Visits
FROM (select visitid,Diag_Code from tblInjury inj, luICD9 icd where icd.Diag_Code *= inj.Injury_Code and icd.Diag_Code like 'E%')i, tblVisit v
where i.visitid = v.visitid
GROUP BY
case when Diag_Code = 'BLAH' then 'BLAH BLAH'
when Diag_Code = 'BAM' then 'BAM BAM'
else 'Unknow' END

 

[Bygbobbo]

select
case
when Diag_Code = 'BLAH' then 'BLAH BLAH'
when Diag_Code = 'BAM' then 'BAM BAM'
else 'Unknow'
END as Cluster,
COUNT(inj.VisitID) AS Visits
from tblInjury inj
right join luICD9 icd ON icd.Diag_Code = inj.Injury_Code
inner join tblVisit v ON inj.visitid = v.visitid
where 0 = 0
and icd.Diag_Code like 'E%'
GROUP BY
case
when Diag_Code = 'BLAH' then 'BLAH BLAH'
when Diag_Code = 'BAM' then 'BAM BAM'
else 'Unknow' END


Im not sure if visitID is coming from inj or icd..

Im also not sure if you are trying to dynamically join to a generated table name. But if your more specific with what your trying to do Im sure all aspects can be accomodated.

The error your getting is more about your non-ansi syntax.

Bygs

 

[ncedddf]

Thanks Bygs

What I'm trying to do is show all Injury Codes from luICD9 with a count of visits from tblInjury with corresponding date from tblvisit. I need the outer join because There are Injury codes that have no visits.

Dennis

 

[zhavic]

Can you post back structure of tables with
just needed columns,
and example of your data and result that you want to get ?

Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Bygbobbo]

Left joining from the table that you want all the records does this.


select
case
when Diag_Code = 'BLAH' then 'BLAH BLAH'
when Diag_Code = 'BAM' then 'BAM BAM'
else 'Unknow'
END as Cluster,
sum(case when v.VisitID is null then 0 else 1) AS Visits
from luICD9 icd
left join tblInjury inj ON icd.Diag_Code = inj.Injury_Code
left join tblVisit v ON inj.visitid = v.visitid
where 0 = 0
and icd.Diag_Code like 'E%'
GROUP BY
case
when Diag_Code = 'BLAH' then 'BLAH BLAH'
when Diag_Code = 'BAM' then 'BAM BAM'
else 'Unknow' END

Bygs