CODEWITHSUNDEEP
sqlsql-serverdatabase
jerry
jerry

Reputation: 129

Return all rows when one value is hit

I have 2 columns, id and date. If the date is in 2/3/16, 2/4/16, 2/5/16, I want to return all values. Hard to explain so here's an example. The following should return all 3 rows for id 1 and none of 2. Any help would be appreciated. Thanks!

id     date
1      2/1/16
1      2/2/16
1      2/3/16
2      2/11/16 
2      2/12/16

Upvotes: 0

Views: 40

Answers (2)

user1
user1

Reputation: 586

This is Oracle syntax; try to rewrite it for SQL Server:

WITH T AS (
SELECT 1 AS ID, TO_DATE('2/1/16', 'DD/MM/YY') AS MY_DATE FROM DUAL UNION ALL
SELECT 1 AS ID, TO_DATE('2/2/16', 'DD/MM/YY') AS MY_DATE FROM DUAL UNION ALL
SELECT 1 AS ID, TO_DATE('2/3/16', 'DD/MM/YY') AS MY_DATE FROM DUAL UNION ALL
SELECT 2 AS ID, TO_DATE('2/11/16',  'DD/MM/YY') AS MY_DATE FROM DUAL UNION ALL
SELECT 2 AS ID, TO_DATE('2/12/16', 'DD/MM/YY') AS MY_DATE FROM DUAL
)
SELECT *
FROM T T1 WHERE EXISTS(
SELECT * FROM T T2
WHERE
 T1.ID = T2.ID
 AND T2.MY_DATE IN (TO_DATE('2/3/16', 'DD/MM/YY'), TO_DATE('2/4/16', 'DD/MM/YY'), TO_DATE('2/5/16', 'DD/MM/YY'))
);

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Try this:

SELECT id, [date]
FROM mytable
WHERE id IN (SELECT Id  
             FROM mytable
             WHERE [date] IN ('2016-03-02', '2016-04-02', '2016-05-02'))

Upvotes: 1

Related Questions