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