[thelist] SQL question

Ken Schaefer ken at adOpenStatic.com
Fri Feb 28 00:09:01 CST 2003


When you say "nothing", do you mean NULL values? If so, you want an OUTER
JOIN. This will return NULL values for the CertTypes columns where there is
no matching CertType value.

That said, I don't think you should be using SELECT *. You'd want to select
a.CertType, but not c.CertType, and return data like:

a.CertType  a.Field1          c.Description
1                'something'     'something'
2                'something'     'something'
NULL          'something'     NULL
3                'something'     'something'

SELECT
    a.CertType,
    a.Field1
    c.Description
FROM
    Gem AS a
LEFT OUTER JOIN
    CertType AS c
ON
    a.CertType = c.CertType

However, if you are intending to use aggregate functions on columns from
table C, then NULLs will not be included in things like AVG() etc. In which
case you will probably want to use COALESCE() to return a non-null default
value (eg 0) or similar.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "noah" <noah at tookish.net>
To: <thelist at lists.evolt.org>
Sent: Friday, February 28, 2003 4:51 PM
Subject: Re: [thelist] SQL question


: Hi Ken,
:
: Thanks for your help.
:
: At 12:21 AM 28/02/2003, you wrote:
: >If a.CertType is not required, then what rows from Certtypes do you want
to
: >return when a.CertType is not set?
:
: Nothing from the CertTypes table, but I still want to return all the
: records from the Gem table. The problem is that as soon as I introduce the
: join between the Gem table and the CertTypes table, the query stops
: returning all records in the Gem table where the CertType field is unset
: (i.e., the field that I'm joining to the CertTypes table).
:
: >Perhaps if you could give us some sample data (a few rows from each
table),
: >plus what you want the expected output to be, we can help you write the
: >query...
:
: Thanks -- it's actually quite convoluted, and I'm afraid that might
further
: confuse the issue (I simplified it significantly in asking my question). I
: think the above explanation is better than my original one -- if not, let
: me know and I'll put together the data.
:
: Here's the query again:
:
: >: SELECT * FROM Gem a, Shapes b, CertTypes c WHERE a.Shape = b.Shape AND
: >: a.CertType = c.CertType AND NumberOfStones > 0 ORDER BY AverageSize
:
: Thanks again,
: Noah
:
: --
: * * Please support the community that supports you.  * *
: http://evolt.org/help_support_evolt/
:
: For unsubscribe and other options, including the Tip Harvester
: and archives of thelist go to: http://lists.evolt.org
: Workers of the Web, evolt !




More information about the thelist mailing list