Results 1 to 3 of 3

Thread: sql query

  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Unanswered: sql query

    Q-In table EMPLOYEE, we have two fields as NAME and EMPNO.
    table is like this

    Table name is EMPLOYEE

    NAME EMPNO
    aaaa 111
    aaaa 222
    bbbb f02
    bbbb 333
    cccc f11
    cccc f00
    dddd ---
    eeee f01
    eeee f03
    eeee 111
    gggg f34
    gggg f23
    gggg 888
    gggg 777


    1. How can i select only those NAME whose EMPNO start with 'F' only.if same NAME contain EMPNO starting with other than 'F' we need not to selct that.
    in above e.g.
    My desired output will be cccc f11
    cccc f00


    but not bbbb f02
    bbbb 333



    2.How can i select only that NAME which contains all EMPNO starting with 'F' but it must have only one EMPNo starting with other than 'F'.
    e.g.
    My desired ouput eeee f01
    eeee f03
    eeee 111


    but not gggg f34
    gggg f23
    gggg 888
    gggg 777




    3.How can i select only that NAME which contains all EMPNO starting with 'F' but it must have more than one EMPNO starting with other than 'F'.
    e.g.
    My desired ouput gggg f34
    gggg f23
    gggg 888
    gggg 777



    but not eeee f01
    eeee f03
    eeee 111


    4.How can i select only that NAME which contains all EMPNO starting without 'F' but it must not have any EMPNO starting with 'F'.
    e.g.

    My desired ouput aaaa 111
    aaaa 222

    but not bbbb f02
    bbbb 333

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    These are all very simple SQL statements. You should probably sign up for an SQL 101 course. Graeme Birchall has a really good SQL cookbook that you can read.
    Here are a couple of the queries you are looking for:
    1. select name, empno from employee a where empno like 'f%' and not exists (select 1 from employee b where a.name = b.name and b.empno not like 'f%')

    2. select distinct name, empno from employee a where empno like 'f%' and 1 = select count(*) from employee b where a.name = b.name and b.empno not like 'f%')

    Dave

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I suggest you try to do your homework on your own. The idea is that you learn this stuff and don't copy some answers. You'll flunk your exams (at least I hop so) if you don't understand this.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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