Mega Code Archive

 

Categories / MSSQL / Select Query

 

ISNULL with subquery

11> CREATE TABLE titles( 12>    title_id       varchar(20), 13>    title          varchar(80)       NOT NULL, 14>    type           char(12)          NOT NULL, 15>    pub_id         char(4)               NULL, 16>    price          money                 NULL, 17>    advance        money                 NULL, 18>    royalty        int                   NULL, 19>    ytd_sales      int                   NULL, 20>    notes          varchar(200)          NULL, 21>    pubdate        datetime          NOT NULL 22> ) 23> GO 1> 2> insert titles values ('1', 'Secrets',   'popular_comp', '1389', $20.00, $8000.00, 10, 4095,'Note 1','06/12/94') 3> insert titles values ('2', 'The',       'business',     '1389', $19.99, $5000.00, 10, 4095,'Note 2','06/12/91') 4> insert titles values ('3', 'Emotional', 'psychology',   '0736', $7.99,  $4000.00, 10, 3336,'Note 3','06/12/91') 5> insert titles values ('4', 'Prolonged', 'psychology',   '0736', $19.99, $2000.00, 10, 4072,'Note 4','06/12/91') 6> insert titles values ('5', 'With',      'business',     '1389', $11.95, $5000.00, 10, 3876,'Note 5','06/09/91') 7> insert titles values ('6', 'Valley',    'mod_cook',     '0877', $19.99, $0.00,    12, 2032,'Note 6','06/09/91') 8> insert titles values ('7', 'Any?',      'trad_cook',    '0877', $14.99, $8000.00, 10, 4095,'Note 7','06/12/91') 9> insert titles values ('8', 'Fifty',     'trad_cook',    '0877', $11.95, $4000.00, 14, 1509,'Note 8','06/12/91') 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> 4> SELECT title, pub_id, ISNULL(price, (SELECT MIN(price) 5>     FROM titles)) FROM titles 6> GO title                                                                            pub_id -------------------------------------------------------------------------------- ------ --------------------- Secrets                                                                          1389                 20.0000 The                                                                              1389                 19.9900 Emotional                                                                        0736                  7.9900 Prolonged                                                                        0736                 19.9900 With                                                                             1389                 11.9500 Valley                                                                           0877                 19.9900 Any?                                                                             0877                 14.9900 Fifty                                                                            0877                 11.9500 (8 rows affected) 1> 2> 3> drop table titles; 4> GO