Reputation: 466
I have this table:
beneficiary service marks term
1 eng 50 1
1 eng 2
1 math 30 1
1 math 20 2
1 com 1
1 com 70 2
2 com 1
2 com 30 2
2 eng 20 1
2 eng 30 2
How can I extract only the rows for beneficiary / service pairs that have marks in both terms in a service?
So from this table it should return only:
beneficiary service mark term
1 math 30 1
1 math 20 2
2 eng 20 1
2 eng 30 2
Upvotes: 0
Views: 117
Reputation: 79794
This works when I import your data into my local database and run it:
SELECT s.*
FROM scores AS s
JOIN (
SELECT beneficiary,service
FROM scores
WHERE marks IS NOT NULL
GROUP BY beneficiary,service HAVING COUNT(*) = 2
) AS x ON (x.beneficiary = s.beneficiary AND x.service = s.service);
Output:
beneficiary | service | marks | term
-------------+---------+-------+------
1 | math | 30 | 1
1 | math | 20 | 2
2 | eng | 20 | 1
2 | eng | 30 | 2
(4 rows)
Upvotes: 3