468,539 Members | 1,496 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

LEFT JOIN returning NULL values?

Hello,

I am trying to perform a LEFT JOIN on a table which may or may not have
matching rows.

If there are no matching rows, it returns NULL's for all the missing fields.

Is there anyway of returning the default values for that table instead of
NULL's, in a portable way?

My query currently looks like this:

SELECT i_product.name, i_product.price, `i_tax-rate`.rate
FROM i_product
LEFT JOIN `i_tax-rate` ON (`i_tax-rate`.`tax-rate-id` =
i_product.`tax-band-id`)
WHERE i_product.id = 123';

Thanks for your thoughts!

Jamie.
Jul 20 '05 #1
1 12370
Jamie Burns wrote:
Is there anyway of returning the default values for that table instead of
NULL's, in a portable way?


Several RDBMS products out there have "system tables" (the term varies)
for using ordinary SQL to query information, about the schema of the
current database, such as the default value for a given field. But the
structure of these system tables is vendor-proprietary. There is no
standard to which the schema of the system tables conforms. Some
RDBMS's, including MySQL as far as I know, don't have this mechanism at
all. I don't think there can be a solution that is portable to any
RDBMS, to query the default value of a given field of a given table
directly.

One thing you could do is to create a conventional table to store the
defaults.

CREATE TABLE defaults (
table_name varchar(32) NOT NULL,
field_name VARCHAR(32) NOT NULL,
default_value VARCHAR(128),
primary key (table_name, field_name)
);
INSERT INTO defaults VALUES ('i_tax-rate', 'rate', '8.25');

It would be up to you to keep the data in this table synchronized with
the actual DEFAULT values defined for fields in your database. You need
to define the datatype for default_value such that it can represent the
default value for any field you track using this table.

Then your query would look like this:

SELECT P.name, P.price, COALESCE(T.rate, D.default_value)
FROM i_product P
INNER JOIN defaults D ON (D.table_name = 'i_tax-rate'
AND D.field_name = 'rate')
LEFT JOIN `i_tax-rate` T ON (T.`tax-rate-id` = P.`tax-band-id`)
WHERE P.id = 123;

Note that if you need to join to the defaults table once per field for
which you need a default value.

(The examples above are not tested; your mileage may vary.)

Regards,
Bill K.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Marek Kotowski | last post: by
1 post views Thread by Paul Bramscher | last post: by
4 posts views Thread by Andrei Ivanov | last post: by
5 posts views Thread by jim | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.