SELECT Statement with AND Operator Confusion

Transact-SQL
1

I've got this simple table:

| stu_award_year_token | fund_ay_token |
|----------------------|---------------|
| 4959967              | 16682         |
| 4959967              | 16684         |
| 4959967              | 17136         |
| 4959967              | 17182         |
| 4959967              | 17269         |
| 4959967              | 17270         |
| 4959967              | 17491         |
| 4968273              | 17270         |
| 4968273              | 17271         |
| 4968273              | 17313         |
| 4968273              | 17417         |
| 4968273              | 17492         |
| 4986979              | 17491         |
| 4986979              | 16684         |
| 4986979              | 18646         |

I'm trying to return only those stu_award_year_token where fund_ay_token is both 17491 and 16684. So, from the example above, only stu_award_year_token 4959967 and 4986979 would be returned.

I know it's something I'm doing wrong with my AND statement. Any suggestions is appreciated!

Query:

select * from stu_award sa
join stu_award_year say on sa.stu_award_year_token=say.stu_award_year_token
join student s on say.student_token=s.student_token
where award_year_token='2015'
and (fund_ay_token='17491' and (fund_ay_token='16684'))
2 
-- *** Consumable Test Data ***
-- Please provide in fuure
CREATE TABLE #t
(
    stu_award_year_token int NOT NULL
    ,fund_ay_token int NOT NULL
);
INSERT INTO #t
VALUES (4959967,16682),(4959967,16684),(4959967,17136),(4959967,17182),(4959967,17269)
,(4959967,17270),(4959967,17491),(4968273,17270),(4968273,17271),(4968273,17313),(4968273,17417)
,(4968273,17492),(4986979,17491),(4986979, 16684),(4986979,18646);
-- *** End Test Data ***

SELECT stu_award_year_token
FROM #t
WHERE fund_ay_token IN (17491,16684)
GROUP BY stu_award_year_token
HAVING COUNT(*) = 2;
3

Thanks, lfor!

I'd like to take it a step further, though. I need to see all stu_award_year_token where fund_ay_token is equal to any of the following: "12345" or "23456" or "34567" or "45678", and then also has a fund_ay_token equal to 17491, if that makes sense?

4

Slight modifications to the code @Ifor posted

SELECT  stu_award_year_token
FROM    #t
WHERE   fund_ay_token IN ( 17491, 123456,23456,34567,45678 )
GROUP BY stu_award_year_token
HAVING
	SUM(CASE WHEN fund_ay_token = 17491 THEN 1 ELSE 0 END) >= 1
	AND 
	SUM(CASE WHEN fund_ay_token IN ( 123456,23456,34567,45678 )THEN 1 ELSE 0 END) >= 1
5

James, this works exactly how I needed it to. Thanks!