SQL Select default value in a query from a table

I have a query which I want to have a default value from a table (you could call it a vlookup in excel). My query looks like this:

select stamdata_sd.id_sd AS id_sd, st开发者_Go百科amdata_sd.Type_sd
AS Type_sd from stamdata_sd;

I want the Type_sd field in the query to be a default value from a table called type_ty. Something like this:

select stamdata_sd.id_sd AS id_sd, stamdata_sd.Type_sd default(selected
from a table called type_ty, where the pk id_ty is the higest)
AS Type_sd from stamdata_sd;

So, when i make a new record in the query, the field Type_sd is auto filled with the newest instance in the type_ty table.

How am I able to write such a query?

---

You could use the ANSI-Standard COALESCE function. This function will test its arguments in order, and return the first of them that is not null.

It would be something in this shape:

select 
  stamdata_sd.id_sd AS id_sd, 
  COALESCE(stamdata_sd.Type_sd, 
    (select max(t.name_of_colum) from type_ty t) ) AS Type_sd 
from stamdata_sd

---

I think this is a really bad design. Your table will always have incorrect data in it. And it will always have some nulls in a column that should probably be NOT NULL.

If I were you, I'd try writing a BEFORE INSERT and BEFORE UPDATE triggers to make sure the right data always goes into the base table. You can SELECT the current value from your type_ty table.

The use of a BEFORE INSERT trigger is obvious. But you also want to make sure good data goes into that column should a user try to set it to NULL or to an empty string. So you need a BEFORE UPDATE trigger, too.

There are also good ways to handle this in client code, after first fixing the data and constraints in the base table. Client code can require a value once a session, which it can use as the current value, or it can remember the last value used and insert it into the user interface (where it might be overtyped with a different value). You could store the current value in a cookie. Lots of ways.

---

So, when i make a new record in the query, the field type_sd is auto filled with the newest instans in the type_ty table.

It seems you want the INSERT statement to grab a particular value from some other table:

               insert into ZOO(animal)
               values
               ( {the most recently added animal in the ANIMALS table} )

One way to grab the most recently added row in a table is to use a TIMESTAMP column, which is updated with the value corresponding to "now" when the row is inserted:

                create table ANIMALS(id integer primary key autoincrement, 
                      animal text, creationdate timestamp)

N.B. How databases handle timestamps varies, so treat the above as pseudo-code.

                insert into ANIMALS(animal) values('lion')

would result in something like this:

                 1,   lion,   2011-09-23 08:00:05.123

You can get the most recently added animal like this (assuming that no two records are created at exactly the same millisecond):

                 select animal from ANIMALS
                 where creationdate = (select max(creationdate) from ANIMALS)

Thus:

               insert into ZOO(animal)
               select animal from ANIMALS 
               where creationdate = (select max(creationdate) from ANIMALS)