-2

I have a table called txns which has attributes txn_dt,user_id,txn_amt. I would need all users who made txns on both dates 08/15/2016 and 08/30/2016.

Is there any way to get all the users from the table by accessing it only once? Pls share your inputs on this. I know that INTERSECT and INNER JOIN will work but I need to access the table twice. Can I solve this problem by accessing table only once? Thank you so much in advance.

Teja
  • 13,214
  • 36
  • 93
  • 155
  • 1
    [edit] your question and add some sample data and the expected output based on that data. [_Formatted_](http://stackoverflow.com/editing-help#code) **text** please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) –  Sep 02 '16 at 06:04
  • Possible duplicate of [SELECTING with multiple WHERE conditions on same column](http://stackoverflow.com/questions/4047484/selecting-with-multiple-where-conditions-on-same-column) – Mani Deep Sep 02 '16 at 06:40

2 Answers2

2

try this:

SELECT user_id 
FROM txns
WHERE txn_dt IN ('2016-08-15'::date, '2016-08-30'::date)
GROUP BY user_id
HAVING COUNT(*) = 2
Mani Deep
  • 1,298
  • 2
  • 17
  • 33
0

try this:

select t.*
from txns t
where (select 1 from txns t1 where t1.user_id = t.user_id 
and t1.txn_dt::date = '2016-08-15'::date limit 1) is not null
and (select 1 from txns t2 where t2.user_id = t.user_id 
and t2.txn_dt::date = '2016-08-30'::date limit 1) is not null;
Piotr Rogowski
  • 3,642
  • 19
  • 24