[thelist] SQL question
rudy
r937 at interlog.com
Fri Feb 28 00:21:01 CST 2003
> SELECT * FROM Gem a, Shapes b, CertTypes c
> WHERE a.Shape = b.Shape
> AND a.CertType = c.CertType
> AND NumberOfStones > 0 ORDER BY AverageSize
>
> I need to test c.Description later in the script, hence the
> a.CertType = c.CertType join. The problem is that a.CertType
> is not a required field, and the above query is not returning
> the records where a.CertType is not set. How can I have it return
> all records, whether a.CertType is set or not?
that requires a left outer join
select a.foo, a.bar
, IIF(ISNULL(b.Shape)
, 'not set',b.Shape) as bShape
, IIF(ISNULL(c.CertType)
, 'not set',c.CertType) as cCertType
from ( Gem a
left outer
join Shapes b
on a.Shape = b.Shape )
left outer
join CertTypes c
on a.CertType = c.CertType
where NumberOfStones > 0
order
by AverageSize
notice that the first join is parenthesized, that's a syntax
requirement unique to microsoft access
when a particular Gem row has no match in Shapes or CertTypes,
the field from Shapes or CertTypes will be null, so the
IIF and ISNULL construction (again, microsoft access syntax)
will generate 'not set' instead
rudy
More information about the thelist
mailing list