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

Expected Result:
Employee-Num
1

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

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')

---

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

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

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')