dcsimg
Results 1 to 6 of 6

Thread: Simple Query help

  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Simple Query help

    Table Employee

    Employee-Num | Type
    1 | A
    1 | B
    2 | A
    3 | B

    From the above table I only want records where an employee has both Type A and Type B

  2. #2
    Join Date
    Mar 2010
    Posts
    5
    Expected Result:
    Employee-Num
    1

    As only Employee number 1 has both Type A and Type B

  3. #3
    Join Date
    Feb 2011
    Location
    Melbourne, Australia
    Posts
    13
    Try this

    Code:
    (SELECT orig.emplNr      
      FROM Employee as orig join Employee as dummy on orig .empl=dummy.empl and dummy.type='B'
      where orig .type='A')

  4. #4
    Join Date
    Mar 2010
    Posts
    5

    getting wrong result

    thanks for your answer gallad...but that gives me wrong result...I am only getting type A with your query.

    what I wanted was result which will give me type A and type B

  5. #5
    Join Date
    Feb 2011
    Location
    Melbourne, Australia
    Posts
    13
    It worked perfectly for me on a test DB .
    The principle used was quite straightforward. The query without the join statement gives all entires of Type B. By joining on the employeenr and demanding the type to be B in the joined table, you efectively only get the employees with type A and B

  6. #6
    Join Date
    Mar 2010
    Posts
    5

    solved

    SELECT orig.emplNr
    FROM Employee as orig join Employee as dummy on orig .empl=dummy.empl and dummy.type='B'
    join Employee as dummy2 on orig.empl = dummy2.empl and dummy2.type = 'A'
    where orig.type in ('A', 'B')

Posting Permissions

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