[thelist] SQL question

[rudy]

> 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