Professional Web Applications Themes

Use of IFNULL in SELECT query with subqueries - MySQL

Hello, I can't get it figured out how to use the IFNULL function with the following query: mysql> SELECT min(a.lowestnum) AS num_new FROM (SELECT id_staff+1 AS lowestnum FROM staff_data) a WHERE a.lowestnum NOT IN (SELECT id_staff FROM staff_data) This gives the lowest free (better: non existing) integer number from a list of non consecutive numbers ("id_staff"). Problem: When the table staff_data is empty at start, I get NULL returned, but want '1' being returned in this case. I tried to do it with IFNULL, but cannot get it to work. Kind of: mysql> SELECT IFNULL(min(a.lowestnum) AS num_new FROM (SELECT id_staff+1 ...

  1. #1

    Default Use of IFNULL in SELECT query with subqueries

    Hello,

    I can't get it figured out how to use the IFNULL function with the
    following query:

    mysql> SELECT min(a.lowestnum) AS num_new FROM (SELECT id_staff+1 AS
    lowestnum FROM staff_data) a WHERE a.lowestnum NOT IN (SELECT id_staff
    FROM staff_data)

    This gives the lowest free (better: non existing) integer number from a
    list of non consecutive numbers ("id_staff").
    Problem: When the table staff_data is empty at start, I get NULL returned,
    but want '1' being returned in this case. I tried to do it with IFNULL,
    but cannot get it to work. Kind of:
    mysql> SELECT IFNULL(min(a.lowestnum) AS num_new FROM (SELECT id_staff+1
    AS lowestnum FROM staff_data) a WHERE a.lowestnum NOT IN (SELECT id_staff
    FROM staff_data), '1');
    ....which does not work :-(

    Any hint is appreciated!

    Thanks,

    Pascal.
    --
    P.A. Guest

  2. #2

    Default Re: Use of IFNULL in SELECT query with subqueries

    On Sat, 21 Jul 2007 16:20:01 +0200, P.A. <com> wrote: 


    SELECT IFNULL(min(a.lowestnum),1) AS num_new FROM (SELECT id_staff+1
    AS lowestnum FROM staff_data) a WHERE a.lowestnum NOT IN (SELECT id_staff
    FROM staff_data);
    --
    Rik Wasmus
    Rik Guest

  3. #3

    Default Re: Use of IFNULL in SELECT query with subqueries

    Am 21.07.2007, 16:47 Uhr, schrieb Rik <com>:
     

    Yep, that works! Thought I tried that before but probably missed something.

    Thanks!

    Pascal.


    --
    P.A. Guest

Similar Threads

  1. query that uses subqueries
    By ros in forum MySQL
    Replies: 5
    Last Post: April 19th, 02:06 PM
  2. Query of Query to select a title first letter
    By Conti in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 18th, 11:42 PM
  3. subqueries in query of queries
    By helenmhudson in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 7th, 04:25 PM
  4. select query help
    By Abul Hasan Lakhani in forum MySQL
    Replies: 8
    Last Post: October 15th, 07:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139